Thanks:  0
Likes:  0

# Thread: I need a formula to negate "time"

1. 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.

2. 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.

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

3. 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. 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..

## User Tag List

#### Posting Permissions

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