Calculate total hours into days, hours, minutes

[Issie_52]

Hi there,

Employee A gets days off for overtime hours worked.

I need a formula (in Columns D) for the total days, hours and minutes based on the hours/minutes in Column B.

A	B	C	D
Time	Add/Deduct	Hrs taken	Total Days/hrs/min
03:30	22:35	0​	2 days, 6 hrs, 35 min (what I want in this column)
01:00	23:35	0​

Formula Column B: B3+A4-C4
Column A & B are put in and calculated as HH:MM:SS

I have search and tried a lot of formulas, but to no avail. Most will only show the days but not the remaining hours and minutes.

Any help would be appreciated. Thank you.

 

[Issie_52]

Hi there,

Employee A gets days off for overtime hours worked.

I need a formula (in Columns D) for the total days, hours and minutes based on the hours/minutes in Column B.

A	B	C	D
Time	Add/Deduct	Hrs taken	Total Days/hrs/min
03:30	22:35	0​	2 days, 6 hrs, 35 min (what I want in this column)
01:00	23:35	0​

Formula Column B: B3+A4-C4
Column A & B are put in and calculated as HH:MM:SS

I have search and tried a lot of formulas, but to no avail. Most will only show the days but not the remaining hours and minutes.

Any help would be appreciated. Thank you.

Forgot to mention the day must be 8 hours long

 

[Georgiboy]

Here is a fairly crude way of doing it:

Book1
	A	B	C	D
1	Time
2		22:35		2 days, 6 hrs, 35 min
3		23:35		2 days, 7 hrs, 35 min
4		08:21		1 days, 0 hrs, 21 min
5		00:36		0 days, 0 hrs, 36 min
6		56:21:00		7 days, 0 hrs, 21 min
Sheet1

Cell Formulas
Range	Formula
D2:D6	D2	=INT(B2/0.333)&" days, "&HOUR(B2-INT(B2/0.333)*"08:00:00")&" hrs, "&MINUTE(B2-INT(B2/0.333)*"08:00:00")&" min"

 

[Issie_52]

Here is a fairly crude way of doing it:

Book1
	A	B	C	D
1	Time
2		22:35		2 days, 6 hrs, 35 min
3		23:35		2 days, 7 hrs, 35 min
4		08:21		1 days, 0 hrs, 21 min
5		00:36		0 days, 0 hrs, 36 min
6		56:21:00		7 days, 0 hrs, 21 min
Sheet1

Cell Formulas
Range	Formula
D2:D6	D2	=INT(B2/0.333)&" days, "&HOUR(B2-INT(B2/0.333)*"08:00:00")&" hrs, "&MINUTE(B2-INT(B2/0.333)*"08:00:00")&" min"

Fantastic, It worked beautifully.

Thank you so much