Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
alexw94
Helper I
Helper I

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

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. 

3 REPLIES 3
mlsx4
Super User
Super User

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)

 

@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".

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)

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors