Converting hours/minutes to working days/hours/minutes

[JohnRuddy]

Hi
I have a spreadsheet which records hours worked and produces a running total of whether this is up or down on contracted hours (37.5 hours a week). The working day is 7 hours 30 minutes.

This total is expressed in hours:minutes (see below). I am trying to get a formula which will show this has days in one cell, hours in the next and minutes in a third. I've tried many of the solutions listed on here (and elsewhere), without success.

In the table below, 66:10 should be converted into 8 days 6 hours 10 minutes.

[TABLE="width: 673"]
<colgroup><col><col span="7"><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Actual No of hours worked during week[/TD]
[TD]Hours required by contract[/TD]
[TD]Balance of time, +/-[/TD]
[TD]Running total of overtime[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 5"][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]60:45:00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Monday[/TD]
[TD]Tuesday[/TD]
[TD]Wednesday[/TD]
[TD]Thursday[/TD]
[TD]Friday[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]27-Nov-17[/TD]
[TD]28-Nov-17[/TD]
[TD]29-Nov-17[/TD]
[TD]30-Nov-17[/TD]
[TD]1-Dec-17[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Start[/TD]
[TD]7:15[/TD]
[TD]7:15[/TD]
[TD]7:15[/TD]
[TD]7:10[/TD]
[TD]7:25[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lunch start[/TD]
[TD]12:00[/TD]
[TD]12:00[/TD]
[TD]12:45[/TD]
[TD]12:00[/TD]
[TD]12:00[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lunch stop[/TD]
[TD]12:15[/TD]
[TD]12:15[/TD]
[TD]13:00[/TD]
[TD]12:15[/TD]
[TD]12:15[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Finish[/TD]
[TD]16:00[/TD]
[TD]16:00[/TD]
[TD]16:30[/TD]
[TD]16:00[/TD]
[TD]16:00[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Time Off[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Actual [/TD]
[TD]Contract[/TD]
[TD]Balance[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD]8:30[/TD]
[TD]8:30[/TD]
[TD]9:00[/TD]
[TD]8:35[/TD]
[TD]8:20[/TD]
[TD]42:55:00[/TD]
[TD]37:30:00[/TD]
[TD]5:25:00[/TD]
[TD]66:10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[FormR]

66:10 should be converted into 8 days 6 hours 10 minutes.

Hi, welcome to the posting side of the forum. Maybe you can adapt this to your set-up - note: You may need to change the format of the cells in which you place the formulas to general or number.

Excel 2013/2016
	A	B	C	D
1	Total	Days	Hours	Minutes
2	66:10:00	8	6	10
Sheet1

Cell Formulas
Range	Formula
B2	=INT(A2/"7:30")
C2	=INT(MOD(A2,"7:30")*24)
D2	=MOD(MOD(A2,"7:30"),"1:00")*1440

 

[JohnRuddy]

Thanks for that! Exactly what I need!

 

[crimsonhobbit]

I came across this solution for an almost identical requirement I had. My implementation works ok except that when the number of days is an exact multiple of the hours and minutes for a standard day (in my case "9:30") rather than display 1 day 0 Hours and 0 Minutes for 1 business day, it displays 1 day 9 hours and 30 minutes effectively adding an additional day. Is there a way of refining the above formulas so this doesn't happen?
Thanks

 

[FormR]

Hi, welcome to the forum!

Is there a way of refining the above formulas so this doesn't happen?

You could try like this instead:

Excel 2013/2016
	A	B	C	D
1	Total	Days	Hours	Minutes
2	09:30	1	0	0
3	19:00	2	0	0
4	28:30	3	0	0
5	38:00	4	0	0
6	47:30	5	0	0
7	57:00	6	0	0
8	66:30	7	0	0
Sheet1

Cell Formulas
Range	Formula
B2	=INT(A2/"9:30")
C2	=INT((A2-(B2*"9:30"))*24)
D2	=INT((A2-("9:30"*B2)-(C2/24))*1440)