Subtracting time depending on text in a previous cell

  • [TABLE="width: 500"]

    [tr]


    [td][/td]


    [td]

    A

    [/td]


    [td]

    B

    [/td]


    [td]

    C

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    Horse

    [/td]


    [td]

    Rider

    [/td]


    [td]

    Time

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td]

    Lucky

    [/td]


    [td]

    John

    [/td]


    [td]

    12:45

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td]

    Venus

    [/td]


    [td]

    Jane

    [/td]


    [td]

    1:45

    [/td]


    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td]

    Zelda

    [/td]


    [td]

    Joe

    [/td]


    [td]

    2:15

    [/td]


    [/tr]


    [/TABLE]



    Im making a scheduling workbook, my current task im trying to accomplish involves organising ride times for horses. Column 1 Contains Horses, Column B riders, and C time. In my scenario if a student is riding they get on the horse 15 minutes prior to the time, if the trainer is riding he gets on at the set time in column C. Can someone help devise a formula that if cells in column B does not contain the word "John" to subtract 15 minutes from the adjoining cell in column C.


    At the point of the document where i will be adding this formula, each cell if populated from another cell in the worksheet. so the cells actually look as such.


    [TABLE="width: 500"]

    [tr]


    [td][/td]


    [td]

    Horse

    [/td]


    [td]

    Rider

    [/td]


    [td]

    Time

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td]

    lucky

    [/td]


    [td]

    =bb60

    [/td]


    [td]

    =bd60

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td]

    venus

    [/td]


    [td]

    =bb61

    [/td]


    [td]

    =bd61

    [/td]


    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td]

    zelda

    [/td]


    [td]

    =bb62

    [/td]


    [td]

    =bd62

    [/td]


    [/tr]


    [/TABLE]



    Any help would be much appreciated, i attempted an =If() formula, and will continue to try make it work unless anyone can help.

  • Re: Subtracting time depending on text in a previous cell


    Perhaps this:


    =IF(BB60="John",BD60,BD60-TIME(0,15,0))

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • Re: Subtracting time depending on text in a previous cell


    Quote from AliGW;783861

    Perhaps this:


    =IF(BB60="John",BD60,BD60-TIME(0,15,0))



    Close, but i need the opposite, if said cell does not contain "John" 15 minutes needs to be subtracted.
    its a start and much appreciated!

  • Re: Subtracting time depending on text in a previous cell


    Have you tried it? That's what it does! :duh:


    Excel 2016 (Windows) 32 bit
    [Table="width:, class:head"][tr=bgcolor:#888888][th][COLOR="#FFFFFF"] [/COLOR][/th][th]

    [COLOR="#FFFFFF"]A[/COLOR]

    [/th][th]

    [COLOR="#FFFFFF"]B[/COLOR]

    [/th][th]

    [COLOR="#FFFFFF"]C[/COLOR]

    [/th][th]

    [COLOR="#FFFFFF"]D[/COLOR]

    [/th][/tr]
    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]1[/COLOR]

    [/td]

    [td]

    Horse

    [/td][td]

    Rider

    [/td][td]

    Time

    [/td][td]

    Formula

    [/td]

    [/tr]


    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]2[/COLOR]

    [/td]

    [td]

    Lucky

    [/td][td]

    John

    [/td][td]

    12:45

    [/td][td]

    12:45:00

    [/td]

    [/tr]


    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]3[/COLOR]

    [/td]

    [td]

    Venus

    [/td][td]

    Jane

    [/td][td]

    01:45

    [/td][td]

    01:30:00

    [/td]

    [/tr]


    [tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

    [COLOR="#FFFFFF"]4[/COLOR]

    [/td]

    [td]

    Zelda

    [/td][td]

    Joe

    [/td][td]

    02:15

    [/td][td]

    02:00:00

    [/td]

    [/tr]
    [/table]
    [Table="width:, class:grid"]

    [tr][td]

    Sheet: Sheet1

    [/td][/tr]

    [/table]

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • Re: Subtracting time depending on text in a previous cell


    I can assure you that it works. I tested it before I made the suggestion. :)

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • Re: Subtracting time depending on text in a previous cell


    I hate to say I told you so! ;)


    You're very welcome. :)

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • Re: Subtracting time depending on text in a previous cell


    Now long shot here, if my formula is " =IF(H40="David",J80,J80-TIME(0,15,0)) " and if it has no data to return and comes back #Value! how can i hide it? Ive used "=IF(ISNA " to hide another formula but can tweak it to hide this formula/error

  • Re: Subtracting time depending on text in a previous cell


    Try this:


    =IFERROR(IF(H40="David",J80,J80-TIME(0,15,0)),"")

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • Re: Subtracting time depending on text in a previous cell


    Then you are using an old version of Excel!


    Try this:


    =IF(ISERROR(IF(H40="David",J80,J80-TIME(0,15,0))),"",IF(H40="David",J80,J80-TIME(0,15,0)))

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • Re: Subtracting time depending on text in a previous cell


    Glad to have helped. :)

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

Participate now!

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