I need a formula to negate "time"

spyldbrat · May 6th, 2002, 06:20 PM

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 · May 6th, 2002, 06:44 PM

Quote:

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 · May 6th, 2002, 06:47 PM

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 · May 6th, 2002, 07:04 PM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 6th, 2002, 06:20 PM	#1
spyldbrat New Member Join Date: May 2002 Posts: 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. Help...please!

May 6th, 2002, 06:47 PM	#3
IML MrExcel MVP Join Date: Feb 2002 Location: Denver, CO Posts: 1,744	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 ]

May 6th, 2002, 07:04 PM	#4
IML MrExcel MVP Join Date: Feb 2002 Location: Denver, CO Posts: 1,744	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..