Hours to Days, Hours & Minutes

[smather]

Hi there

I'm looking to do a formula that calculates hours to days, hours and minutes, but it's a little wonky and not quite straight forward.

There's are two type of work days - one is 9 hours and the other is 7hrs 30 mins. 

Workday Type 1 = 7hrs 30

Workday Type 2 = 9

Data looks a little like:

Name	Project	Hours	WorkDay Type
Person 1	123	03:45	1
Person 1	852	08:00	2
Person 1	N/A	00:30	N/A
Person 2	123	08:15	1
Person 2	741	02:50	2
Person 3	852	03:00	2

So what I'd expect from this would be: 

Project 123: (12 Hours) 1 Day, 4 Hours, 30 Mins

Project 741: (2 Hours) 2 Hours

Project 852: (11 Hours) 1 Day, 2 Hours

And then, if possible, in another formula, to translate that to decimal days (rounding up/down as needed), so using the examples above, I'd expect: 

Project 123: 1.75 (1 full day and roughly about 3/4ths of a day)

Project 741:  0.15 (nearly qtr of a day)

Project 852: 1.15 (I think that's right, my maths isn't the best, sorry)

Thank you!!

[amitchandak]

@smather , First assume you have measure sim of hours

Total = sum(Table[Hours])

or

Total = sumX(Table, Hour([Time]) + Minute([Time])/60)

Then you can have the measure

Total Decimal = Quotient([Total ],8) +MoD([Total ],8)/8

Total Text= Quotient([Total ],8) & " Days " & MoD([Total ],8)/8 & " Hours"

[smather]

Hi @amitchandak 

Yes, I already have a Sum(Table[hours]) 🙂 

Is the 8 in your formula the working day? Because depending on the type of workday, it could either be 7h30m or 9h - could you explain it a little more for me please?

[v-chenwuz-msft]

Hi @smather ,

You can use SWITCH() to change the denominator where is 8 in that formula.

SWITCH( SELECTEDVALUE( 'Table'[WorkDay Type]),
1, 7.5,
2, 9 )

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.