 # Subtracting from time

• I'm trying to calculate from a set time to get a starting time. For example, say in Cell A1 end time is 9PM & I need to subtract 8 hours from that to get to 1PM. I can't seem to figure it out. Using Time() the result comes out as ####. I'm only being given a number value to subtract from the end time.

Any suggestions?

• Re: Subtracting from time

Hi Snuzer,
If cell A1 has 9pm, if I wanted to subtract 8 hours (i.e. .3333 or 1/3'd of a day) this is what I would do using VBA:

Code
``range("B1") = CDbl(range("A1"))-.333``

This changes cell A1 into a double value and then it subtracts .3333 (one third of a day) and puts the value into call B1.

If you just want a cell formula ... then put "=A1-0.3333" into cell B1 to get the same result

• Re: Subtracting from time

To determine the correct fraction (i.e. 8 = .3333) just divide the number of hours by 24. this table shows you the value for each whole hour

[TABLE="width: 326"]

[tr]

[TD="align: center"]hour[/TD]
[TD="align: center"]Fraction[/TD]
[TD="align: center"]time change from cell A1[/TD]

[/tr]

[tr]

[TD="align: center"]1[/TD]
[TD="align: center"]0.04166667[/TD]
[TD="align: center"]8:00:00 AM[/TD]

[/tr]

[tr]

[TD="align: center"]2[/TD]
[TD="align: center"]0.08333333[/TD]
[TD="align: center"]7:00:00 AM[/TD]

[/tr]

[tr]

[TD="align: center"]3[/TD]
[TD="align: center"]0.125[/TD]
[TD="align: center"]6:00:00 AM[/TD]

[/tr]

[tr]

[TD="align: center"]4[/TD]
[TD="align: center"]0.16666667[/TD]
[TD="align: center"]5:00:00 AM[/TD]

[/tr]

[tr]

[TD="align: center"]5[/TD]
[TD="align: center"]0.20833333[/TD]
[TD="align: center"]4:00:00 AM[/TD]

[/tr]

[tr]

[TD="align: center"]6[/TD]
[TD="align: center"]0.25[/TD]
[TD="align: center"]3:00:00 AM[/TD]

[/tr]

[tr]

[TD="align: center"]7[/TD]
[TD="align: center"]0.29166667[/TD]
[TD="align: center"]2:00:00 AM[/TD]

[/tr]

[tr]

[TD="align: center"]8[/TD]
[TD="align: center"]0.33333333[/TD]
[TD="align: center"]1:00:00 AM[/TD]

[/tr]

[tr]

[TD="align: center"]9[/TD]
[TD="align: center"]0.375[/TD]
[TD="align: center"]12:00:00 AM[/TD]

[/tr]

[tr]

[TD="align: center"]10[/TD]
[TD="align: center"]0.41666667[/TD]
[TD="align: center"]11:00:00 PM[/TD]

[/tr]

[tr]

[TD="align: center"]11[/TD]
[TD="align: center"]0.45833333[/TD]
[TD="align: center"]10:00:00 PM[/TD]

[/tr]

[tr]

[TD="align: center"]12[/TD]
[TD="align: center"]0.5[/TD]
[TD="align: center"]9:00:00 PM[/TD]

[/tr]

[tr]

[TD="align: center"]13[/TD]
[TD="align: center"]0.54166667[/TD]
[TD="align: center"]8:00:00 PM[/TD]

[/tr]

[tr]

[TD="align: center"]14[/TD]
[TD="align: center"]0.58333333[/TD]
[TD="align: center"]7:00:00 PM[/TD]

[/tr]

[tr]

[TD="align: center"]15[/TD]
[TD="align: center"]0.625[/TD]
[TD="align: center"]6:00:00 PM[/TD]

[/tr]

[tr]

[TD="align: center"]16[/TD]
[TD="align: center"]0.66666667[/TD]
[TD="align: center"]5:00:00 PM[/TD]

[/tr]

[tr]

[TD="align: center"]17[/TD]
[TD="align: center"]0.70833333[/TD]
[TD="align: center"]4:00:00 PM[/TD]

[/tr]

[tr]

[TD="align: center"]18[/TD]
[TD="align: center"]0.75[/TD]
[TD="align: center"]3:00:00 PM[/TD]

[/tr]

[tr]

[TD="align: center"]19[/TD]
[TD="align: center"]0.79166667[/TD]
[TD="align: center"]2:00:00 PM[/TD]

[/tr]

[tr]

[TD="align: center"]20[/TD]
[TD="align: center"]0.83333333[/TD]
[TD="align: center"]1:00:00 PM[/TD]

[/tr]

[tr]

[TD="align: center"]21[/TD]
[TD="align: center"]0.875[/TD]
[TD="align: center"]12:00:00 PM[/TD]

[/tr]

[tr]

[TD="align: center"]22[/TD]
[TD="align: center"]0.91666667[/TD]
[TD="align: center"]11:00:00 AM[/TD]

[/tr]

[tr]

[TD="align: center"]23[/TD]
[TD="align: center"]0.95833333[/TD]
[TD="align: center"]10:00:00 AM[/TD]

[/tr]

[tr]

[TD="align: center"]24[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]9:00:00 AM[/TD]

[/tr]

[/TABLE]

• Re: Subtracting from time

how would i do this with only formulas? i dont have any experience with vba. how would it work if i wanted to know what the time would be if i subtracted from 1AM?

• Re: Subtracting from time

If you type in A1 - "1 am"
Type in B1 - "9 am"
Type in C1 "=B1 - A1"
Format C1 as hh:mm - Result will be 8:00 (representing 8 hours)
Dont use the "" - just what is inside the " "

Regards
[SIZE=3]Anthony
[/SIZE]&WCF_AMPERSAND[SIZE=3]
[/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]

• Re: Subtracting from time

im actually trying to go backwards...kinda:

A1 = 1 am
B1 = 8 hours
C1 = ???

Need to know what C1 would be if i subtract 8 hours or a given set of hours from 1 am

• Re: Subtracting from time

C1 should have this formula ──► =A1-(B1/24)

• Re: Subtracting from time

You need to ensure that cell A1 has a full date as well as the time because you are subtracting back to the previous date

A1 = 1/29/2014 1:00
B1 = 8
C1 should have this formula ──► =A1-(B1/24)

Format cell C1 (and A1 if you want) to TIME

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!