Hours and minutes to days and minutes

[Sambrowne]

Hopefully nice and straightforward although I've scoured the web and can't seem to find the answer I want. I have a spreadsheet with outstanding leave time in hours and minutes that I want to convert to days and hours/minutes where 8 hours is one day. So for D2 I'd like it to read 10 days 2 hours, D3 8 days 30 mins, D4 2 days 7 hours and so on (ignore the minus value in D5).

Thanks in advance!

 

[lokayee]

 

[Sambrowne]

Cheers, for some reason it's not working for me but i think it could be to do with the number format in hours/minutes. So when I put your formula in:

=INT(LEFT(C2,2)/8)&" DAYS "&LEFT(C2,2)-(INT(LEFT(C2,2)/8)*8)&" HRS "&RIGHT(C2,2)&" MINS"

I get this:

When I click in C2 I get this in the formula bar:

Checking the formatting shows that the hours are:

I'm guessing I would need to change the formatting to get your formula to work? Thanks again.

 

[Dave Patton]

T202101a.xlsm
	B	C	D	E
1	Time	82:00:00	10 day(s) 2 hour(s) 0 minute(s)	10 days 02:00 hh:mm
2	Time	64:30:00	8 day(s) 0 hour(s) 30 minute(s)	8 days 00:30 hh:mm
3
4	Text	82:00:00	10 day(s) 2 hour(s) 0 minute(s)	10 days 02:00 hh:mm
5	Text	64:30:00	8 day(s) 0 hour(s) 30 minute(s)	8 days 00:30 hh:mm
2f

Cell Formulas
Range	Formula
D4:D5,D1:D2	D1	=INT(C1*3)&" day(s) "&HOUR(MOD(C1*3,1)/3)&" hour(s) "&MINUTE(MOD(C1*3,1)/3)&" minute(s)"
E4:E5,E1:E2	E1	=INT(C1*3)&" days "&TEXT(MOD(C1*3,1)/3,"hh:mm")&" hh:mm"

 

[lokayee]

 

[Sambrowne]

You guys are scary, how do you learn to do this stuff?!

Thanks!

 

[Dave Patton]

Thanks for the feedback.