Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: I need a formula to negate "time"

  1. #1
    Board Regular
    Join Date
    May 2002
    Posts
    116
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    I recently did a spreadsheet for someone and I became stumped on something, thinking it can't be done. Then I remembered, there is some sort of formula that can help me, but I don't know how to write it.

    Column A & B are in hr:mm format. Both columns are subtracted to give an answer in column C (same format as Column A & B is used). There are times when the column C # will actually be a negative. Because time is never really negative, I get an error.

    The spreadsheet should be working as follows:

    An employee is alloted x amount of time to complete something (Column A), but actually does is y amount of time (Column B). I need to know if the employee did better or worse than what was allotted.

    Help...please!

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,021
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-05-06 17:20, spyldbrat wrote:
    I recently did a spreadsheet for someone and I became stumped on something, thinking it can't be done. Then I remembered, there is some sort of formula that can help me, but I don't know how to write it.

    Column A & B are in hr:mm format. Both columns are subtracted to give an answer in column C (same format as Column A & B is used). There are times when the column C # will actually be a negative. Because time is never really negative, I get an error.

    The spreadsheet should be working as follows:

    An employee is alloted x amount of time to complete something (Column A), but actually does is y amount of time (Column B). I need to know if the employee did better or worse than what was allotted.

    Help...please!
    Switch to the 1904 date system, if you can, thru Tools|options.

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    edit
    I think
    =ABS(A1-B1)
    would get you the same thing.


    One would be to give the difference in time by using something like
    =MAX(A1:B1)-MIN(A1:B1)

    then you could use something like
    =IF(A1>B1,"under","over")
    in column D

    [ This Message was edited by: IML on 2002-05-06 17:51 ]

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    One other thought, if you aren't married to you format, how about a simple
    =(A1-B1)*24 for hour
    or
    =(A1-B1)*1440 for minutes
    with a general format?

    Then you'd have a column to sort by.

    My mantra today - post first, think second..

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •