# simple question (subtracting time)..



## Hoffman (Dec 26, 2022)

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 (Dec 26, 2022)

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 (Dec 26, 2022)

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 (Dec 26, 2022)

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.xlsmABCD14 hours 12 mins 8 Sec2 hours 8 min 7 sec2:04:012 hours 04 min 1 sec212 hours 42 mins 8 Sec8 hours 50 min 40 sec3:51:283 hours 51 min 28 sec311bCell FormulasRangeFormulaC1:C2C1=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:D2D1=C1


----------



## rollis13 (Dec 26, 2022)

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 (Dec 26, 2022)

Hi sorry
Excel for Mac v16.56

I will try both and report back.

Thanks
Barry


----------



## Dave Patton (Dec 26, 2022)

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

Time.xlsmABCDEFGHIJKLMNO44 hours 12 mins 8 Sec4hours12mins8Sec2 hours 8 min 7 sec2hours8min7sec2:04:01512 hours 42 mins 8 Sec12hours42mins8Sec8 hours 50 min 40 sec8hours50min40sec3:51:2811bCell FormulasRangeFormulaO4:O5O4=TIME(B4,D4,F4)-TIME(I4,K4,M4)


----------



## bebo021999 (Dec 26, 2022)

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


----------



## Hoffman (Dec 26, 2022)

rollis13 said:


> 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 (Dec 26, 2022)

Dave Patton said:


> If you do not have the Functions TextBefore and TextAfter, try Data TextToColumns   with separator space.
> 
> Time.xlsmABCDEFGHIJKLMNO44 hours 12 mins 8 Sec4hours12mins8Sec2 hours 8 min 7 sec2hours8min7sec2:04:01512 hours 42 mins 8 Sec12hours42mins8Sec8 hours 50 min 40 sec8hours50min40sec3:51:2811bCell FormulasRangeFormulaO4:O5O4=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


----------



## Hoffman (Dec 26, 2022)

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 (Dec 26, 2022)

bebo021999 said:


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



I am not sure what you are asking?
Barry


----------



## Hoffman (Dec 26, 2022)

rollis13 said:


> 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 (Dec 26, 2022)

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

Time.xlsmABC74 hours 12 mins 8 Sec2 hours 8 min 7 sec2:04:01812 hours 42 mins 8 Sec8 hours 50 min 40 sec3:51:2811bCell FormulasRangeFormulaC7C7=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))C8C8=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 (Dec 26, 2022)

Dave Patton said:


> What have you tried?
> The following may provide some ideas that you can work with.
> 
> Time.xlsmABC74 hours 12 mins 8 Sec2 hours 8 min 7 sec2:04:01812 hours 42 mins 8 Sec8 hours 50 min 40 sec3:51:2811bCell FormulasRangeFormulaC7C7=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))C8C8=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 (Dec 27, 2022)

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 (Dec 27, 2022)

rollis13 said:


> 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 (Dec 27, 2022)

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


----------



## GraH (Dec 27, 2022)

Book1ABCDEFGHIJK14 hrs 7 min 8 sec2 hrs 2 min 3 sec4784:07 AM2232:02 AM2 hrs 05 min 05 secSheet1Cell FormulasRangeFormulaC1:E1C1=TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(A1," ","</s><s>")&"</s></t>","//s[position()=1 or position()=3 or position()=5]"))F1,J1F1=TIME(C1,D1,E1)G1:I1G1=TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(B1," ","</s><s>")&"</s></t>","//s[position()=1 or position()=3 or position()=5]"))K1K1=F1-J1Dynamic array formulas.


----------



## smozgur (Dec 27, 2022)

Hoffman said:


> Got it. Just as needed.
> 
> Thanks so much.
> You are talented and so helpful.
> ...


The marked solution post has been switched accordingly.


----------

