Calculating a Target time from an Actual Time...

[GIJoeActionMan]

Hi guys,

I've formatted my cells to 'custom' then 'hh:mm:ss.00' so that bit is fine.
The problem I have is my target time cell could read 00:01:30.00 (which
means no hours, 1 minute, 30 seconds, and no hundreds of a second), and my
actual collumn could read 00:01:00.00 (which is 1 minute).

So in my Difference collumn which is also formatted in hh:mm:ss.00 I have
made the formula:

=IF(Q21=0,"",Q21-P21)

which gives me -0.0003472, clearly not in time.

Then in my difference collumn to display the % of the difference in time, I
have this formula:

=IF(Q21=0,"",Q21/P21)

which gives my difference expressed as a % as 67%.

If the actual is 1 minute and the target was 1 min 30 secs then I've
improved by 30 secs, so I'm not sure if this is 67% as my maths aren't too
good ?! Can I assume this is the right percentage between a minute and a
minute and a half ?

And the second thing is, why is it not expressing the time difference, and
just displaying the -0.0003472 ?!

I hope I've explained it clearly to you. I appriciate your help, thanks a lot!

 

[Fred Smith]

A 30 second improvement on a 90 second time is a 33% improvement, not 67%.
Your problem is that you are need to calculate the difference in your
formula, like:
=(P21-Q21)/P21

or, use your difference result, but you don't tell us what column it's in,
so we can only guess:
=R21/P21

I don't know exactly what is causing your formatting problem. Are you using
the 1900 or 1904 date system? In the 1900 date system, you cannot display a
negative time. The 1904 date system will display a negative time, but I
don't use it, so can't tell you exactly what it does.

Regards,
Fred

 

[Dave Peterson]

There is a gotcha in this response.

If you format the cell with the formula as time, you'll see the result in time.

Here comes the gotcha...

If you have a negative value and format it to show time, you may see ####'s.

You can fix this by changing the base date.

In xl2003 menus, it's:
Tools|Options|Calculation tab
Check the "1904 date system" checkbox.

But now all your dates in this workbook are going to be off by 4 years and one
day.

And this setting causes trouble when copying data from one workbook to another
(with the other base date setting).

You have a couple of options. You could return text that look like a
number/time:

=IF(Q21=0,"",if(q21<p21,"-","")&text(abs(Q21-P21),"hh:mm:ss.000"))

Or you could use that setting (and avoid other workbooks).

If you have dates in this workbook that you want to fix...

(saved from a previous post)

You could use the 1904 base date
(in xl2003 menus)
Tools|Options|calculation tab|check "1904 date system"

Be aware that your dates will now be off by 4 years and one day.

And copying data (dates and times) between workbooks with different base dates,
will be a big problem.

One way to add (or subtract) those four years back is to find an empty cell, put
1462 into that cell.

Copy that cell.

Select your range that contains the dates. Edit|PasteSpecial|click Add (or
subtract) (in theoperation box) and check values.

You may want to do it against a copy...just in case.

Most windows users use 1900 as the base date. Mac users (mostly??) use 1904 as
the base date.