simple question (subtracting time)..

[Hoffman]

Thanks in advance..
I have always gotten great help from this fantastic community...

I would like a way to subtract time amounts (not actual time of day) from 2 cells.

So A1 says 4 hours 12 min 8 sec
B1 says 2 hours 8 min 7 sec

and
c1 needs to be the difference?

C1 2 hours 4 min 1 sec (want excel to do, right now I am doing by hand)

so in short, in the cells I need to write the amount of hours min and seconds and then I need a way to find the difference in cells.

thanks very much and wishing terrifichealth for 2023

Barry

 

[Hoffman]

Are there cases of:
4 hours 5 mins
8 mins 6 sec
5 hours 8 sec
5 mins
...

I am not sure what you are asking?
Barry

 

[Hoffman]

Hi to all.
With this format in A1 and B1: 4 hours, 2 min, 1 sec use in C1:

=HOUR(TIMEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," hours, ",":")," min, ",":")," sec",""))-TIMEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1," hours, ",":")," min, ",":")," sec","")))&" hours, " & MINUTE(TIMEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," hours, ",":")," min, ",":")," sec",""))-TIMEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1," hours, ",":")," min, ",":")," sec","")))&" min, " & SECOND(TIMEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," hours, ",":")," min, ",":")," sec",""))-TIMEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1," hours, ",":")," min, ",":")," sec","")))&" sec"

think may work now- still trying

assuming all is working, could you help me with 1 last thing please.
can we clean up the code you sent a bit so instead of the cell saying

4 hours, 2 min, 1 sec

I want to type them in to say this

4 hr 2 min 1 sec

do I need the comas there? if I do I will keep them but if not I'd rather it just be

4 hr 2 min 1 sec

thanks so much!

Barry

 

[Dave Patton]

What have you tried?
The following may provide some ideas that you can work with.

Time.xlsm
	A	B	C
7	4 hours 12 mins 8 Sec	2 hours 8 min 7 sec	2:04:01
8	12 hours 42 mins 8 Sec	8 hours 50 min 40 sec	3:51:28
11b

Cell Formulas
Range	Formula
C7	C7	=TIME(LEFT(A7,FIND(" ",A7)-1),MID(A7,FIND("m",A7)-3,2),MID(A7,FIND("e",A7)-3,1))-TIME(LEFT(B7,FIND(" ",B7)-1),MID(B7,FIND("m",B7)-3,2),MID(B7,FIND("e",B7)-3,1))
C8	C8	=TIME(LEFT(A8,FIND(" ",A8)-1),MID(A8,FIND("m",A8)-3,2),MID(A8,FIND("e",A8)-3,1))-TIME(LEFT(B8,FIND(" ",B8)-1),MID(B8,FIND("m",B8)-3,2),MID(B8,FIND("e",B8)-4,2))

 

[Hoffman]

What have you tried?
The following may provide some ideas that you can work with.

Time.xlsm
	A	B	C
7	4 hours 12 mins 8 Sec	2 hours 8 min 7 sec	2:04:01
8	12 hours 42 mins 8 Sec	8 hours 50 min 40 sec	3:51:28
11b

Cell Formulas
Range	Formula
C7	C7	=TIME(LEFT(A7,FIND(" ",A7)-1),MID(A7,FIND("m",A7)-3,2),MID(A7,FIND("e",A7)-3,1))-TIME(LEFT(B7,FIND(" ",B7)-1),MID(B7,FIND("m",B7)-3,2),MID(B7,FIND("e",B7)-3,1))
C8	C8	=TIME(LEFT(A8,FIND(" ",A8)-1),MID(A8,FIND("m",A8)-3,2),MID(A8,FIND("e",A8)-3,1))-TIME(LEFT(B8,FIND(" ",B8)-1),MID(B8,FIND("m",B8)-3,2),MID(B8,FIND("e",B8)-4,2))

I like those.
What Rollis13 works for me too

his code is
=HOUR(TIMEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(I39," hrs, ",":")," min, ",":")," sec",""))-TIMEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(H39," hrs, ",":")," min, ",":")," sec","")))&" hrs, " & MINUTE(TIMEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(I39," hrs, ",":")," min, ",":")," sec",""))-TIMEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(H39," hrs, ",":")," min, ",":")," sec","")))&" min, " & SECOND(TIMEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(I39," hrs, ",":")," min, ",":")," sec",""))-TIMEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(H39," hrs, ",":")," min, ",":")," sec","")))&" sec"


now I am just trying to learn if I can get rid of all commas so cells look like
A1 4 hrs 13 min 6 sec
B1 2 hrs 9 min 5 sec
C1 2 hrs 4 min 1 sec

notice all commas gone.

possible using his code?

thanks!

Barry

 

[rollis13]

In my formula, wherever you see hrs, or min, just type hrs and min without the commas (leaving the spaces before and after, sec goes with only the leading space).

 

[Hoffman]

In my formula, wherever you see hrs, or min, just type hrs and min without the commas (leaving the spaces before and after, sec goes with only the leading space).

Got it. Just as needed.

Thanks so much.
You are talented and so helpful.
Happy New year!

thanks!
Barry

 

[rollis13]

Thanks for the positive feedback, glad we were able to help.

 

[GraH]

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	4 hrs 7 min 8 sec	2 hrs 2 min 3 sec	4	7	8	4:07 AM	2	2	3	2:02 AM	2 hrs 05 min 05 sec
Sheet1

Cell Formulas
Range	Formula
C1:E1	C1	=TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(A1," ","</s><s>")&"</s></t>","//s[position()=1 or position()=3 or position()=5]"))
F1,J1	F1	=TIME(C1,D1,E1)
G1:I1	G1	=TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(B1," ","</s><s>")&"</s></t>","//s[position()=1 or position()=3 or position()=5]"))
K1	K1	=F1-J1
Dynamic array formulas.

 

[smozgur]

Got it. Just as needed.

Thanks so much.
You are talented and so helpful.
Happy New year!

thanks!
Barry

The marked solution post has been switched accordingly.