Forum Discussion

Icon for Helper III rank

Helper III

9 years ago

Solved

DIFFERENCE BETWEEN DAYS AND HOURS

Hello, can you help me?

I have two DATE / TIME columns.

Column A - DATE TIME BEGINNING

Column B - FINAL TIME DATE

example.

Column A - 10/12/2017 1:00 p.m.

Column B - 10/14/2017 1:30 PM

I would like a DAX formula or by PowerQuery to return the following.

Column B - Column A = 48h: 30m or 48:30.

I tried with datediff but it did not work.

Thank you

v-jiascu-msft
9 years ago
Hi walnei,

Neither 48h: 30m nor 48:30 is a date/time format. It's a text format actually. You can format it like this:
HMformat = VAR minutes = DATEDIFF ( [Column1], [Column2], MINUTE ) RETURN CONCATENATE ( CONCATENATE ( INT ( minutes / 60 ), ":" ), MOD ( minutes, 60 ) )

Best Regards!
Dale

3 Replies

Anonymous
Not applicable
9 years ago
Datetime is stored as a decimal number where the Whole number portion represents the days since 1899. The decimal portion referecens the percentage of a day. I.e. midday is 0.5
v-jiascu-msft
Microsoft Employee
9 years ago
Hi walnei,

Neither 48h: 30m nor 48:30 is a date/time format. It's a text format actually. You can format it like this:
HMformat = VAR minutes = DATEDIFF ( [Column1], [Column2], MINUTE ) RETURN CONCATENATE ( CONCATENATE ( INT ( minutes / 60 ), ":" ), MOD ( minutes, 60 ) )

Best Regards!
Dale
MattAllington
Community Champion
9 years ago
date diff should work. Just format the new column as decimal numbers