There are many scenarios for working with dates in Google Sheets. Maybe you need to compile a list of all employees hired before 2008. Or maybe you want to calculate how long an employee had been working for the company. There is even a Sheets function to calculate the number of whole workdays between two dates.
An important note about working with dates in Google Sheets
It is important to know that Sheets stores dates as consecutive integers: January 1, 1900 is assigned the number 1 and each day thereafter is simply consecutive. For example, April 28, 2015, is stored as 42122. That consecutive number is called the serial number. Cell formatting causes that serial number to be displayed as a date. For example, July 6, 2006’s serial number is 38904. The short date format displays it as 7/6/2006. Also, times are stored as decimals: For example, noon on 7/6/20016 is stored as 38904.5.
Copying the practice workbook
If you are beginning this course at this point, you will need to copy the practice workbook to your Google Drive.
To begin copying the practice workbook in a new browser window,
The file begins to open in a new window of the web browser. Before the document will open and can be edited, we need to make a copy of it.
Move to the new browser window.
To copy the workbook file, in the browser window,
Click
A copy of the file is now open and, by default, is saved in your Google Drive. The filename now has the words "Copy of" at the beginning.
Using the YEAR function
In this data set, the employees' hire dates are in month/day/year format in column E. But what if we just need to know the year that an employee was hired? The YEAR function makes this easy.
The syntax of the YEAR function is:
=YEAR(serial_number)
In this function, the serial_number is the date we wish to use. This will often be a cell reference.
Before we find the year of the employees' hire dates, let's format a column to hold the data.
To add the column heading, in cell L1, type:
Year Hired
To move to cell L2, on the keyboard, press:
Enter key
To enter the YEAR function, type:
=YEAR(E2) Enter key
The correct year, 2018, appears in cell L2.
Now let's copy the YEAR function to the rest of column L.
To select cell L2, on the keyboard, press:
Up Arrow key
To copy the function to the rest of column L,
Double-Click
To deselect the cells,
Click in a blank area of the worksheet
Using the YEARFRAC function
What if we want to know how long an employee has been with the company? The YEARFRAC function can calculate this for us. If we combine that function with the TODAY function, we will get an exact amount of time. In addition, the TODAY function is dynamic and will be updated to the current date whenever the workbook is opened.
The syntax of the YEARFRAC function is as follows:
=YEARFRAC(start_date, end_date, [basis])
In this syntax, basis is optional. This argument tells Excel how to count the days. There are five possibilities for the basis argument:
Basis
Calculation
0 (default)
US (NASD) 30/360
1
actual/actual
2
actual/360
3
actual/365
4
European 30/360
Basis and corresponding calculation
Basis
Calculation
Basis
Calculation
0 (default)
US (NASD) 30/60
1
actual/actual
2
actual/360
3
actual/365
4
European 30/360
By default, the basis is US(NASD) 30/360. This assumes twelve 30-day months for a total of 360 days in a year. This type of date is often used in accounting.
Because we want the exact amount of time between today and the hire date, we will use basis 1. This basis argument includes all of the days between the dates including leap years.
We will combine the YEARFRAC function with the TODAY function. The TODAY function returns the current date. It doesn't require any arguments but does need to be followed by empty parentheses. It is dynamic and will update each time the workbook is opened.
We will enter the following function in cell M2:
=YEARFRAC(TODAY(),E2,1)
To add a column heading to column M, in cell M1 type:
Time of Employment
To move to cell M2 on the keyboard, press:
Enter key
To begin adding the YEARFRAC function, type:
=YEARFRAC(
To add the TODAY function, type:
TODAY(),
To add the start date, type:
E2,
To add the basis argument, type:
1)
To enter the function, on the keyboard, press:
Enter key
The number of years between the current date and the hire date appears in cell M2. This is most likely a fraction.
NOTE: Because the TODAY function is dynamic, the time of employment will be different depending on when this lesson is being completed.
Let's copy the function to the rest of column M.
To select cell M2, on the keyboard, press:
Up Arrow key
To copy the function to the rest of column M,
Double-Click
NOTE: You will see a different number in cell M2.
Select column M, if necessary.
To reduce the number of decimal places, on the toolbar,
Click seven times
To deselect the cells,
Click in a blank area of the worksheet
The time of employment for each employee is now in column M.
Sorting data
With the time of employment calculated for each employee, let's sort the data so the employee with the longest time of employment is at the top of the list. When sorting data in Google Sheets, it is not necessary to select the data to be sorted. Simply select a single cell in the column you wish to sort.
To select a value in the Time of Employment column,
Click any cell in column M
To sort the data with the largest value at the top, in the menu bar
Click Data, Point Sort sheet, Click Sort sheet by column M (Z to A)
The data is now in order by Time of Employment. Dunstano Hurtado Barela was hired on 5/10/2011 and is the employee with the longest employment.
We are finished with the Employees dataset. We can move on to the next exercise.
