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.

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.
   REFERENCE:
             https://blogs.msdn.microsoft.com/samlester/2017/04/06/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