Extracting date and time components

Data Analysis in Google Sheets

James Chapman

Curriculum Manager, DataCamp

Data analysis process

 

 

The data analysis process, with the cleaning and preparing data phase highlighted.

Data Analysis in Google Sheets

Cleaning and preparing data

 

  • 80/20 rule: 80% cleaning, 20% analyzing

 

  • A clean dataset...

    • can be easily processed during analysis
    • will return valid conclusions
    • save more time during analysis

A mop and bucket.

Data Analysis in Google Sheets

Dates and times

  • Collected for measurements over time

A calendar and clock.

  • Continuous data: can take any value
  • Discrete data: can take one of a finite number of categories
Data Analysis in Google Sheets

Discrete dates and times

Question: How do coffee purchases vary over days of the week?

A chart showing the number of coffees purchased over different days of the week.

Data Analysis in Google Sheets

Extracting the year component

 

Syntax:

  • YEAR(date)

 

List of manned moon landing dates.

Data Analysis in Google Sheets

Extracting the year component

 

Syntax:

  • YEAR(date)

 

Extracting the year from the moon landings dates.

Data Analysis in Google Sheets

Extracting the year component

 

Syntax:

  • YEAR(date)

 

Extracting the year from the moon landings dates.

Data Analysis in Google Sheets

Extracting the year component

 

Syntax:

  • YEAR(date)

 

Extracting the year from the moon landings dates.

Data Analysis in Google Sheets

Extracting the month component

 

Syntax:

  • MONTH(date)

 

Extracting the month from the moon landings dates.

Data Analysis in Google Sheets

Extracting the month component

 

Syntax:

  • MONTH(date)

 

Other desirable month formats:

  • E.g., Jan and January

 

Extracting the month from the moon landings dates.

Data Analysis in Google Sheets

Extracting the short month name component

 

Syntax:

  • TEXT(number, format)

 

Extracting the short month name from the moon landings dates.

Data Analysis in Google Sheets

Extracting the short month name component

 

Syntax:

  • TEXT(number, format)

 

  • format → "mmm"

 

Extracting the short month name from the moon landings dates.

Data Analysis in Google Sheets

Extracting the short month name component

 

Syntax:

  • TEXT(number, format)

 

  • format → "mmm"

 

Extracting the short month name from the moon landings dates.

Data Analysis in Google Sheets

Extracting the short month name component

 

Syntax:

  • TEXT(number, format)

 

  • format → "mmm"

 

Extracting the short month name from the moon landings dates.

Data Analysis in Google Sheets

Extracting the long month name component

 

Syntax:

  • TEXT(number, format)

 

  • format → "mmmm"

 

Extracting the long month name from the moon landings dates.

Data Analysis in Google Sheets

Extracting the long month name component

 

Syntax:

  • TEXT(number, format)

 

  • format → "mmmm"

 

Extracting the long month name from the moon landings dates.

Data Analysis in Google Sheets

Extracting the weekday component

 

Syntax:

  • WEEKDAY(date, [type])
    • type: the numbering system to use
      • 1 (default): Start Sunday = 1
      • 2: Start Monday = 1
      • 3: Start Monday = 0

 

Using WEEKDAY to find the weekday of the moon landings.

Data Analysis in Google Sheets

Extracting the weekday component

 

Syntax:

  • WEEKDAY(date, [type])
    • type: the numbering system to use
      • 1 (default): Start Sunday = 1
      • 2: Start Monday = 1
      • 3: Start Monday = 0

 

Using WEEKDAY to find the weekday of the moon landings.

Data Analysis in Google Sheets

Extracting the short weekday name

 

Syntax:

  • TEXT(number, format)

 

  • format → "ddd"

 

Extracting the short weekday name from the moon landings.

Data Analysis in Google Sheets

Extracting the long weekday name

 

Syntax:

  • TEXT(number, format)

 

  • format → "dddd"

 

Extracting the long weekday name from the moon landings.

Data Analysis in Google Sheets

Extracting time components

 

Also many functions to extract time components:

  • HOUR(time)
  • MINUTE(time)
  • SECOND(time)

A clock.

Data Analysis in Google Sheets

Let's practice!

Data Analysis in Google Sheets

Preparing Video For Download...