Custom Column Type

[ngadiez]

Hi All,

I want to get the difference between 2 dates,  StartDate and EndDate columns. (format are date for both)

I am using the 'Add Custom Column' in Power Query.

=Number.RoundUp(([EndDate]-[StartDate])/365)

I get the error here stated we cannot convert the value #duration(2,0,0,0) to type number.

May I know what is the workaround?

Thank you.

[MarcelBeug · Accepted solution]

The corrected formula In Power Query would be:

=Number.RoundUp(Duration.Days([EndDate]-[StartDate])/365)

Otherwise this would not be my prefered way to calculate years between dates.

[himanshu56]

@ngadiez

Custom Column = DATEDIFF([End Date], [Start Date], DAY)

Make sure Start and end date are in Date/Time format and DAY should not be a function.

Thanks,

Himanshu