Scenario
This blog looks at how to use Excel’s undocumented DATEDIF() function to calculate durations between two dates. It works in almost the same way as the DATEDIFF() function used in the DAX language of Power BI.
For one of our master data sets, we needed to calculate how many months each of our staff had been working at our organisation. We needed to know the precise number of CALENDAR months for accounting purposes, to include both whole and part months.
We knew each employee’s join date and leave date, so on paper this looked like a straightforward task. Finding the number of days between two dates is easy, as you simply deduct the ‘Join Date’ from the ‘Leave Date’. However, expressing this as a number of months is not so straightforward, as months have different numbers of days.
We found that traditional methods, such as dividing the total number of days by 30 and rounding to the nearest whole number, were unsatisfactory because they gave approximate results rather than definitive ones – handling part-months was particularly tricky, especially around February time when the month is shorter than average.
That was when we discovered DATEDIF().
DATEDIF() is not documented in Excel’s Help function. You will not see it appear in the formula bar when you start typing. It is a hidden function, available only to those in the know. And it happens to be exactly what we needed for this task.
What DATEDIF() does
This function is used for calculating the number of WHOLE days, months or years between two dates. The syntax of the function is:
=DATEDIF( start_date, end_date, interval)
Interval can be days (expressed as “d”), months (expressed as “m”) or years (expressed as “y”).
In the example below, the DATEDIF() function is used to count the number of days between two dates.
This gives the same result as simply subtracting one date from another, which we can do without DATEDIF(). From now on, we’ll focus only on calculating the difference in months, as this is where the function comes into its own.
Using the DATEDIF() function
Using a similar formula in cell E3, we can find the difference in whole months, as below.
Our data set needs to show the number of months employed. In the above example, we’d expect the count to be ‘2’, i.e. July and August of 2013. Since DATEDIF() only calculates the difference, we need to add a ‘+1’ function to the end of the formula.
Dragging this down gives us the correct result for row 4, but on row 5 we would expect the result to be ‘26’.
Remember, DATEDIF() only counts WHOLE months. Since the ‘start_date’ in this example is the 17th of April, the function is calculating its count based on the 17th of each month. The remaining part-month (i.e. from 17/04/16 to 10/05/16) is not counted.
We can achieve the outcome we need by ‘normalising’ the Join Date field to the first of the month. For simplicity’s sake, we will add this as part of the DATEDIF() formula.
In my Excel Calendar blog, I looked at how to use the DATE() function to calculate the first day of the month. I’ll use the same function here (For a full explanation of how this works, please refer to the Excel Calendar blog).
As you can see, this now gives us the expected outcome in cell E5. It is counting the number of calendar months where an employee was on the books, including any part-months.
Unfortunately, dragging this formula down to rows 6-8 results in a #NUM! error because the ‘Leave Date’ field is blank.
Fixing the #NUM! Errors
We still want to capture the number of calendar months worked for these employees. For current members of staff, column E should display a “running total” of months worked. For this reason, we shall use the TODAY() function.
The following formula will check whether cell D3 has a value in it, and if not, substitute in today’s date.
=IF($D3<>"",$D3,TODAY())
Inserting this conditional statement into our formula now gives us the following result:
This gives us the number of calendar months that each member of staff has been employed for, which fulfils our brief. And it all stems from a function that Excel keeps hidden!
NOTE: For reasons known only to Microsoft, the DATEDIF() function is compatible with all versions of Excel except the version included with Microsoft Office 2007 SP2. If you are running Excel 2007 SP2, this function may not work as expected for you.
Download The Workbook
DATEDIF() – Workbook
Formula Breakdown
There are three parts to the DATEDIF() function, and they are as follows:
Start_Date
Normalises the ‘Join Date’ field to start counting from the first of the month
=DATEDIF(DATE(YEAR($C3),MONTH($C3),1),
End_Date
Returns either the ‘Leave Date’ value or today’s date if this field is blank
IF($D3<>"",$D3,TODAY()),
Interval
Instructs the function to count the number of months (“m”), +1 to include the starting month
"m")+1
Get The Formula
The full formula used in this blog is as follows:-
=DATEDIF(DATE(YEAR($C3),MONTH($C3),1),IF($D3<>"",$D3,TODAY()),"m")+1
Comments