Forum Discussion
Calculate Difference Between Two Date/Time Values Like in Excel
Hi all,
I searched this forum to find out how to calculate the difference between two date and time values and there are plenty of post regarding this topic. (I think) I understand how DATEDIFF works but this is not exactly what I want as this function rounds the results to an integer value.
I need to calculate the difference between two date and time values in the "Excel way". See the example below.
Date1: 05.01.2018 20:49:00
Date2: 08.01.2018 8:46:00
If I subtract Date1 from Date2 in Excel, the result is 2.4979 - this is the correct result. If I use DATEDIFF(List1[Date 1].[Date];List1[Date 2].[Date];DAY) in PowerBI, the result is 3 - this is not the correct result for me. Also I cannot just simply subtract two values like in Excel because the results is some kind of a nonsense like 01.01.1900 11:57:00.
Could you please help me how to get the result that is a real, not rounded number?
Thank you, Martin Madaj.
OK, I got it - my fault! I found the solution a minute after I posted this topic.
A new Custom Column has to be added in Edit Query mode. Then I can simply subtract two date/time values and change the result column data type to Decimal Number.
Best regards,
M.
3 Replies
OK, I got it - my fault! I found the solution a minute after I posted this topic.
A new Custom Column has to be added in Edit Query mode. Then I can simply subtract two date/time values and change the result column data type to Decimal Number.
Best regards,
M.
Hi there
Can you please expand on this two processes you mentioned
1) How did you subtract two date/time value ? did you use "DATEDIFF" ?
2) How did you "change the result column data type to Decimal Number?
Hi there,
Can you please expand on this two processes that you mentioned
1) How did you subtract two date/time values ? did you use "DATEDIFF"?
2) How did you change the result data type to Decimal Number ?
Thanks
