Quote of the Day

The first rule of compounding – never interrupt it unnecessarily.

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 DATEIF 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.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
/* 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 /* The key issue is that the length of each month varies. This complicates computing the number of days. For example, suppose your end date is 3-March-2019 and your start date is 31-July-2019. The month of February does not have a 31st day, so you must detect that case and terminate the month at the end of February. */ Temp = Date.EndOfMonth(#date(if (Date.Month(end) + DayFactor)=0 then Date.Year(end)-1 else Date.Year(end),if (Date.Month(end) + DayFactor)=0 then 12 else Date.Month(end) + DayFactor,1 )), lastDay = List.Min({Date.Day(start), Date.Day(Temp)}), BirthdayFactor = if Date.DayOfYear(start)<=Date.DayOfYear(end) then 0 else 1, DayFactor= if Date.Day(end)<Date.Day(start) then -1 else 0, AC = [ Years = Date.Year(end)- Date.Year(start)-BirthdayFactor, Months = Date.Month(end)-Date.Month(start) + 12*BirthdayFactor + DayFactor, Days= Number.From(end)-Number.From(#date(if (Date.Month(end) + DayFactor)=0 then Date.Year(end)-1 else Date.Year(end), if (Date.Month(end) + DayFactor)=0 then 12 else Date.Month(end) + DayFactor, lastDay)) ] in AC in fDateDif |