Forum Discussion

bparikh's avatar
bparikh
Regular Visitor
5 years ago
Solved

Date difference in Months - Power Query

Hello Experts,   I've two DateTime columns and am trying to find the difference in Months by adding a new column but getting an error. I'm using = Date.Month ([Date1] - [Date2]) Date1 & Date2 bot...
Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

    bparikh 

    if you need the number of months between the dates then add the following, it should work for Date/Time

     = Number.Round(Number.From(([Date 2] - [Date 1])/( 365.25 / 12 )) ,0 )

     

bparikh's avatar
bparikh
Regular Visitor
5 years ago

Wow - it worked ! Thanks a lot Fowmy.

 

Just curious, what was wrong in my approach. 

edhans's avatar
edhans
Community Champion
5 years ago

Subtracting dates in Power Query doesn't return days bparikh - it returns a duration, that is why I used the Duration function in my solution. Fowmy converted the dates to numbers (same numbers you'd see in Excel with an unformatted date) to do the days. You should check out the Duration functions like Duration.TotalDays I used. They are a bit more flexible when a time component comes in, and can be very powerful. Also helps you understand how Power Query handles dates, times, and the differences between them.