Working With Dates

Common Formulas

YEAR, MONTH, DAY, EOMONTHS, WORKDAYS, NETWORKDAYS, TODAY()

Transform To Numeric

Oftentimes, it is beneficial to avoid dates altogether and transform the data to their numeric counterpart. This prevents any possible inconsistencies in terms of display style (1/1/2021, 01/01/2021, 1-1-2021, 2021-01-01, or Jan 1, 2021?), which could be problematic if the data is ever imported to a different program.

A simple way to make this transformation is to use the various date functions built into Excel. The below image is a good example. First extract the YEAR, MONTH, and DAY. Ensure the month and day values are two digits by adding a preceding zero when needed. Concatenate the whole sequence with the ‘&’ operator and finally wrap the entire string with NUMBERVALUE. Voila, MM/DD/YYYY to YYYYMMDD!

Extract Pieces

In addition to the simple formulas shown in the previous section (YEAR, MONTH, DAY), one nice thing about leaving data in date form is Excel’s ability to extract pieces of information on the date, such as the day of the week listed. The below image shows how this information can be further formatted with the TEXT and WEEKDAY functions. Note the second argument in the WEEKDAY function— “1” starts the week on Sunday (i.e. Friday is the 6th day of the week), “2” starts the week on Monday (i.e. Friday is the 5th day of the week), etc.

End Of Months

Another benefit of leaving data in date form is Excel’s ability to specify the ending of a month. Instead of having to piece together different ending days of months and account for leap years, Excel enables users to automatically arrive at the end of the month (or a month “x” months in the future or past) for any inputted date with the EOMONTH function.

This is illustrated in the image below. Note that ROW acts as expected, returning the row number of a given cell reference (ROW(A1) returns 1, ROW(A2) returns 2, etc.). So see that the month-end value of the current date is given with a second argument of 0, the previous month-end value of a given date is given with a second argument of -1, the next month’s month-end value is given with a second argument of 1, etc.

Days Between

Normally, one can use simple subtraction in Excel to get the difference in days between two dates

A1=1/1/2021   #EXAMPLE#
B1=1/2/2021   #Example#
=B1-A1            #Returns 1#

However, it is sometimes useful to get the number of workdays between dates. Imagine we are given the start and end dates for a project and want to determine the number of workdays in each month that intersect with the start and end dates for the project (see the below image).

To make the formula more clear, we can use Excels name manager to avoid constantly using cell references. To do so, we navigate from “Formulas” to ”Name Manager” and create a name for the given cell references as shown below.

With that out of the way, we can work from the inside out of the function shown in the first image of this section. We first test if the last day of the month is before or after the end date of the project. If the last day of the given month is past the end date of the project, we find the number of workdays between the first day of the month, and the end date of the project.

A1=4/30/2022                         #example#
A2=5/16/2022                         #example#
=A1+1                                     #returns 5/1/2022# 
=NETWORKDAYS(A1+1, A2)   #returns 10#

If the last day of the month is before the end of the project and after the beginning of the project, we count the number of workdays between the start of the project and month end date, and then subtract the sum of all the previous month’s workdays.

A1=1/1/2022                     #example#
A2=3/31/2022                   #example#
A3=4/30/2022                   #example#
=NETWORKDAYS(A1, A3)  #returns 86#
=NETWORKDAYS(A1,A2)   #returns 64, difference is 22#

We also note that if the last day of the month is greater than the end date of the project, and a different month from the end date of the project, we return a negative number. To avoid this, we simply wrap the IF statement with LET to avoid negative returns.

A1=5/16/2022                                               #example#
A2=6/30/2022                                               #example#
=NETWORKDAYS(A2,A1)                               #returns -33#
=LET(x, NETWORKDAYS(A2,A1), IF(x<0,0,x))  #returns 0#

Workday Increment

Building on the above, we might want to label where a certain date falls in the number of workdays in a month. Take the below example

First, we ignore all weekends using the WEEKDAY function described in the above sections—if the given date is a Saturday or Sunday, we leave the workday blank. Next, we account for any holidays—if there is a holiday on the date, we list the holiday and don’t count it as a workday. Finally, if the date is neither a weekend or a holiday, we increment the date from the last such date.