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


    Google is your friend - http://office.microsoft.com/en-us/excel-help/add-or-subtract-time-HA102809662.aspx

    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


    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!