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

 

[GraH]

Time is a number so A1-B1 would do. Format column as time. That should do it.

EDIT: Owe, wait the cells contain the time value literally as x hours y mins and z secs?

 

[Hoffman]

yes, I saw something like this online but they had the info in the cell separated by commas I think

I want to do the same so like
4 days, 2 hours, 1 min

 

[Dave Patton]

You do not show the version of Excel that you are using.
The following uses functions from the latest version of Excel.
D1 custom format h "hours" mm "min" s "sec"

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

Cell Formulas
Range	Formula
C1:C2	C1	=TIME(TEXTBEFORE(A1," "),TEXTAFTER(TEXTBEFORE(A1," ",3)," ",2),TEXTAFTER(TEXTBEFORE(A1," ",5)," ",4))-TIME(TEXTBEFORE(B1," "),TEXTAFTER(TEXTBEFORE(B1," ",3)," ",2),TEXTAFTER(TEXTBEFORE(B1," ",5)," ",4))
D1:D2	D1	=C1

 

[rollis13]

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"

 

[Hoffman]

Hi sorry
Excel for Mac v16.56

I will try both and report back.

Thanks
Barry

 

[Dave Patton]

If you do not have the Functions TextBefore and TextAfter, try Data TextToColumns with separator space.

Time.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
4	4 hours 12 mins 8 Sec	4	hours	12	mins	8	Sec	2 hours 8 min 7 sec	2	hours	8	min	7	sec	2:04:01
5	12 hours 42 mins 8 Sec	12	hours	42	mins	8	Sec	8 hours 50 min 40 sec	8	hours	50	min	40	sec	3:51:28
11b

Cell Formulas
Range	Formula
O4:O5	O4	=TIME(B4,D4,F4)-TIME(I4,K4,M4)

 

[bebo021999]

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

 

[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"

Thanks- I tried this and I copied your formula into C1, it gave me back #value which I think means an error in the code?
any suggestions?

Barry

 

[Hoffman]

If you do not have the Functions TextBefore and TextAfter, try Data TextToColumns with separator space.

Time.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
4	4 hours 12 mins 8 Sec	4	hours	12	mins	8	Sec	2 hours 8 min 7 sec	2	hours	8	min	7	sec	2:04:01
5	12 hours 42 mins 8 Sec	12	hours	42	mins	8	Sec	8 hours 50 min 40 sec	8	hours	50	min	40	sec	3:51:28
11b

Cell Formulas
Range	Formula
O4:O5	O4	=TIME(B4,D4,F4)-TIME(I4,K4,M4)

Dave I am trying to keep this to just 3 cells only
A1 4 hrs, 7 min, 8 sec
B1 2 hrs, 2 min, 3 sec
C1 has the formula to subtract the two cells.

thoughts?

Barry