Convert time

[GColeman]

Hello. I have a sheet that utilizes time in 24hr format. There are some time calculations that result in how many hours an employee has left to work, and displays it in the 24hr format.
Is there a way to split the value and add text to have the cell show "11hrs 30 mins" rather than "11:30"? Currently I can get it to show " You have 11 hrs and 01 mins left to work" but how do I get it to show 30 mins instead of the 01? I assume the "(D7,"mm")" is where the problem is.
I get that I may need to split the "11:30:00" imto multiple cells to pull the data from. I can do that through the data tab, but it doesn't seem to be dynamic (F9:G9). Or with a formula using LEFT, MID, RIGHT (F11) but I get #VALUE error when I try and do it with a number. I definately prefer something like the formula I have in F7 though.

 

[AhoyNC]

Try:

Book1
	D	E	F
6
7	11:30:00		You have 11 hours and 30 mins left to work.
8
Sheet1

Cell Formulas
Range	Formula
F7	F7	="You have "&TEXT(HOUR(D7),"#")&" hours and "&TEXT(MINUTE(D7),"#")&" mins left to work."

 

[GColeman]

And this is why I love this page.
Thank you.

 

[GColeman]

And this is why I love this page.
Thank you.

I may have tagged it as fixed too soon. It doesn't work if the time remaining is over 24 hours. 25:30 gives an answer of "1 hours and 30 mins" As well, and less of an issue, it doesnt recognize :00 as minutes. 25:00 gives an answer of "1 hours and mins".
D7 is formatted [h]:mm:ss

 

[GColeman]

Try:

Book1
	D	E	F
6
7	11:30:00		You have 11 hours and 30 mins left to work.
8
Sheet1

Cell Formulas
Range	Formula
F7	F7	="You have "&TEXT(HOUR(D7),"#")&" hours and "&TEXT(MINUTE(D7),"#")&" mins left to work."

[/RANGE

I tried a few variations but still wasn't able to get it to recognize it as hours, rather than time of day. Any additional thoughts?

 

[AhoyNC]

See if this works. If not what's the formula you have in D7.

Book1
	B	C	D	E	F
7
8	2/20/2024 15:06	2/20/2024 8:46	06:20		You have 6 Hours and 20 min left to work
9	2/20/2024 11:00	2/20/2024 10:26	00:34		You have 0 Hours and 34 min left to work
10	2/20/2024 11:00	2/20/2024 10:00	01:00		You have 1 Hours and 0 min left to work
11	2/20/2024 1:00	2/19/2024 11:00	14:00		You have 14 Hours and 0 min left to work
12	2/20/2024 2:00	2/17/2024 13:15	2.53125		You have 60 Hours and 45 min left to work
Sheet1

Cell Formulas
Range	Formula
D8:D12	D8	=B8-C8
F8:F12	F8	="You have " &TEXT(INT(IF(INT(D8)>=1,(INT(D8)*24)+HOUR(D8-INT(D8)),HOUR(B8-C8))),0)&" Hours and "&TEXT(MINUTE(D8),0)&" min left to work"

 

[rlv01]

Perhaps modify cell F7 like this:

="You have "&TEXT(INT(C7) * 24 + HOUR(C7),"#")&" hours and "&TEXT(MINUTE(C7),"0")&" mins left to work."

 

[AhoyNC]

@riv01 That is a shorter formula. You need to change the "#" in your formula to "0" so that it shows 0 hours as in F9.

Book1
	B	C	D	E	F
9	2/20/2024 11:00	2/20/2024 10:26	00:34		You have 0 hours and 34 mins left to work.
Sheet1

Cell Formulas
Range	Formula
D9	D9	=B9-C9
F9	F9	="You have "&TEXT(INT(D9) * 24 + HOUR(D9),"0")&" hours and "&TEXT(MINUTE(D9),"0")&" mins left to work."

 

[Rick Rothstein]

Maybe this formula...

=TEXT(D9,"""You have ""[h]"" hours and ""m"" minutes left to work""")

 

[GColeman]

@riv01 That is a shorter formula. You need to change the "#" in your formula to "0" so that it shows 0 hours as in F9.

Book1
	B	C	D	E	F
9	2/20/2024 11:00	2/20/2024 10:26	00:34		You have 0 hours and 34 mins left to work.
Sheet1

Cell Formulas
Range	Formula
D9	D9	=B9-C9
F9	F9	="You have "&TEXT(INT(D9) * 24 + HOUR(D9),"0")&" hours and "&TEXT(MINUTE(D9),"0")&" mins left to work."

This one and the last solution both seem to work. I'll keep them both handy in case i run into other issues. Thank you for the help.