Power Query DATEDIF Function

Quote of the Day

The first rule of compounding – never interrupt it unnecessarily.

Charlie Munger

Figure 1: DATEDIF Workaround From Ashish Mathur.

Figure 1: DATEDIF Workaround From Ashish Mathur.

I have been using Excel's DATEDIF function for years to determine the age of items in years, months, and days. Until I ran into a bug last week, I did not know that the function was unsupported by Microsoft and had issues with calculating the number of days. Because much of my personal work involves dates, I need to have an accurate age calculation function for use in Excel and Power Query. In this post, I will discuss a DATEDIF workaround that I found online (Figure 1) and a Power Query age calculation function that I wrote based on a concept from Imke Feldmann. My workbook is available here for those who are interested. The workbook shows how I tested the routine by comparing it with the DATEDIF workaround results. I tested the boundary conditions and then random dates. The results agreed with the DATEDIF workaround of Figure 1 and an online date calculator.

As far as the DATEDIF workaround, I will leave you to examine Figure 1 for an example of the problem and the workaround proposed by Ashish Mathur. Please see his blog post on the issue for more details.

The source code for my Power Query function to compute the ages is shown below. The function is fed a column of start and end dates and produces a column of records that contain years, months, and days. You just expand this column as you need. The workbook contains an example of applying the function.

/* FUNCTION: Emulate Excel's DATEDIF function in Power Query for computing 
             age in terms of years, months, days.
   INPUTS:   start : table column containing date at the beginning of the date interval
             end   : table column containing date at the end of the date interval.
   OUTPUT:   fDateDif: a record placed into each entry of a table column. This record can be 
             expanded to select the year, month, or days in the date interval.
Calculating the difference between two dates in YEARS, MONTHS, and DAYS in Power BI or Excel
*/ let fDateDif = (start as date,end as date) => let /* Determine the Number of Years */ StartRef = #date(1964, Date.Month(start),Date.Day(start)), EndRef = #date(1964, Date.Month(end),Date.Day(end)), YearCorr = if EndRef<StartRef then 1 else 0, Years_0 = Date.Year(end)-Date.Year(start)-YearCorr, /* **************************************************** */ /* Determine the Number of Months */ MonthCorr = if Date.Day(EndRef)<Date.Day(StartRef) then 1 else 0, temp = Date.Month(EndRef)-Date.Month(StartRef)-MonthCorr, Month_0 = if temp<0 then temp+12 else temp, /* **************************************************** */ /* Determine the Number of Days */ DateDiff = Date.Day(end)-Date.Day(start), DaysPrevMonth = if Date.Month(end)=1 then 31 else Date.Day(Date.EndOfMonth(#date(Date.Year(end),Date.Month(end)-1,1))), Days_0 = if DateDiff<0 then DateDiff+DaysPrevMonth else DateDiff, Combined = [ Years = Years_0, Months = Month_0, Days = Days_0 ] in Combined in fDateDif
This entry was posted in Excel. Bookmark the permalink.

5 Responses to Power Query DATEDIF Function

  1. Lluis Faja says:

    I like your M function, but I found some cases didn't give good results:

    Start=31/05/2019 and End=30/05/2020 --> 1 year, -1 month and 30 days
    Start=06/07/2015 and End=05/07/2020 --> 5 years, -1 months and 29 days

    Could you analyze these examples ?
    Thanks in advance

    • mathscinotes says:

      Thanks for catching a bug. Give me a few days to get back to i.


    • mathscinotes says:

      I just posted an update. Could you check it out? I greatly expanded my test set and all looked good.

      • Lluis Faja says:

        Hi Mark

        Could you revise the example:
        Start=31/07/2019 and End=01/03/2020 --> 0 years, 7 months and -1 days

        Why you use 1964 in your algorithm ?

        • mathscinotes says:

          Very interesting case. I tested my routine by running a large number of random cases against Excel's DATEDIF (I assumed it was correct). Turns out that DATEDIF gives the same wrong result. I will check this out.

          1964? I could have used any year that had every possible date. So I just needed a leap year.

          Thanks for the help.

          NOTE: I found that this is an issue that has been discussed online with DATEDIF (link). I will use Timeanddate.com for my comparison (link). It appears to give the correct result.


Comments are closed.