Negative Time 1

[shadowfather]

Hi,

I have a spreadsheet that subtracts a time in H:M:S from another time H:M:S. The problems start when I get a negative time it just shows #####. Can I get a negative time to show?

 

[DPlank]

It needs to be formatted as [h]:mm:ss

You won't get an answer unless you change the calculation system to 1904 (in Tools->Options->Calculation).

Then it works - I built a flex balance calculation system around this a few years back.


Cheers,
Dave

Probably the only Test Analyst Manager on Tek-Tips...therefore whatever it was that went wrong, I'm to blame...

animadverto vos in Abyssus!

Take a look at Forum1393!

 

[johnnygeo]

A cheap workaround, if you don't need the result to be an actual time value, is to do something like this:

=IF(A1>B1,A1-B1,"(" & TEXT(A1-B1,"[h]:mm")&")")

where A1 and B1 contain start and end times, respectively. In the event of a negative result, we reverse the subtraction order and display parentheses (or you could use a - instead) to denote negativity. Obviously, you won't be able to perform subsequent calculations on this difference.

 

[SkipVought]

Dave,

So YOU'RE the one we have to blame for this 1900 system NOT working so well, eh?
[sub]Should'v known -- suspected him all along -- mumble grumble hrump grrrrrr.......[/sub]

Skip,
[sub]
[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH! [/sub]

 

[DPlank]

Skip,

Yep. Gotta keep it interesting...

Cheers,
Dave

Probably the only Test Analyst Manager on Tek-Tips...therefore whatever it was that went wrong, I'm to blame...

animadverto vos in Abyssus!

Take a look at Forum1393!