Need to work out difference between two columns that contain DD/MM/YYYY HH:MM:SS values

[alexw94]

Hi,

I have 2 columns that contains DD/MM/YYYY HH:MM:SS values and I need to work out the [H]:MM:SSS difference.... The columns are named [Time] & [Alarm Time] and [Alarm Time] will always be greater than [Time]

For example, if [Time] was 01/01/2023 00:00:00 and [Alarm Time] 03/01/2023 00:00:00 - this would need to be 48:00:00 rather than 2 days. 

Any suggestions on how I can create this succesfully would be greatly apperciated. 

[mlsx4]

Hi @alexw94 

You can do something like:

= Table.AddColumn(#"YourPreviousStepName", "Diff", each Duration.Days([Alarm Time]-[Time])*24+ Duration.Hours([Alarm Time]-[Time])+Duration.Minutes([Alarm Time]-[Time])/60+Duration.Seconds([Alarm Time]-[Time])/3600)

[alexw94]

@mlsx4  - Thank you for your reply. The "YourPreviousStepName" bit confuses me. The previous step in my applied steps was adding a custom column thats irrelevant to these 2 columns. I did type out the name of that previous step in that but "Added Custom 1" and i get the error saysing "Make sure the previous step is spelt correctly".

[mlsx4]

Hi  @alexw94 

You need to include the previous step name just because M follows an order... Anyway, if it isn't clear for you, you can add a custom column and add only this part: 

Duration.Days([Alarm Time]-[Time])*24+ Duration.Hours([Alarm Time]-[Time])+Duration.Minutes([Alarm Time]-[Time])/60+Duration.Seconds([Alarm Time]-[Time])/3600)