I need a formula to negate "time"

[spyldbrat]

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!

 

[Aladin Akyurek]

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.

 

[IML]

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

 

[IML]

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