Time formulas

[hotkeyz]

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I'm trying to set up a worksheet that assesses results against targets in particular speed of service in a fast food situation.

For example if we have a target peed of service set at 2 minutes 30 seconds, then I need a formula to calculate the difference between an actual result and the target.

I have only been able to do this by entering the times into the worksheet as a decimal figure (e.g. 2.45 rather than 2:45) and using rather complicated lengthy formulas to translate into numbers which represent the time in seconds as a decimal number (e.g. 2.45 is 165 seconds). Then use these numbers for other calculations which then need to be converted back to a time represented as a decimal number.

I have not been able to do anything successfully using time format since an error is returned when you get a negative result - as in the speed of service time achieved is better than the target.

Any recommendations?

 

[Jim Gordon Mac MVP]

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor:
Intel I'm trying to set up a worksheet that assesses results against
targets in particular speed of service in a fast food situation.

For example if we have a target peed of service set at 2 minutes 30
seconds, then I need a formula to calculate the difference between an
actual result and the target.

I have only been able to do this by entering the times into the
worksheet as a decimal figure (e.g. 2.45 rather than 2:45) and using
rather complicated lengthy formulas to translate into numbers which
represent the time in seconds as a decimal number (e.g. 2.45 is 165
seconds). Then use these numbers for other calculations which then need
to be converted back to a time represented as a decimal number.

I have not been able to do anything successfully using time format since
an error is returned when you get a negative result - as in the speed of
service time achieved is better than the target.

Any recommendations?

Hi,

Excel does time calculations as decimals, so all you need to do is make
a lookup table. A whole number is an hour, so your table would have a
column for minutes that starts at zero and goes to 60 minutes. 15
minutes is .25, 30 minutes is .5 etc. Do a similar thing for the seconds
and then use VLOOKUP to get the decimal result instantly as you enter
the times in minutes and seconds. Subtract one decimal from the other,
and then to the reverse with another lookup.

-Jim