There are many scenarios for working with dates in Excel. 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 an Excel function to calculate the number of whole workdays between two dates.
Available Files
The following files are available for download:
An important note about working with dates in Excel
It is important to know that Excel 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. For more information about how Excel handles dates and times, see the Microsoft Support article: TIME function.
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
- To save the workbook, on the keyboard, press:
Control key + S
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/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 move to the Home tab, on the Ribbon,
Click the Home tab
- To reduce the number of decimal places, on the Home tab,
Click
seven times - To deselect the cells,
Click in a blank area of the worksheet
- To select the columns, in the column header,
Press & drag from L to M
- To resize the columns,
Double-Click between the column headers
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 Excel, 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,
Right-Click the selected cell, Point Sort, Click Sort Largest to Smallest
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 close it and move on to the next exercise.
- To save the workbook, on the keyboard, press:
Control key + S
- Close the Employees workbook.