Calculate the difference between two times (excel in office xp)

[Guest]

I follow the ms instrucs and always get the answer #VALUE!
WHY??? I have tried formatting the answer cell in every way possible but with no success.
Thanks for any help.

 

[Norman Harker]

Hi Gerrand!

Just try the following:

Format A1:C1
hh:mm:ss

A1:
=NOW()
Leave it a couple of minutes
B1:
=NOW()
C1:
=B1-C1

C1 should look something like:
00:02:43

If you have problems with the above, then report back with what you
have.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
news:[email protected]...

 

[Norman Harker]

Hi Gerrand!

Of course. Sorry I was just trying a certain way of getting a time
into a couple of cells.

Your answer though appears to be correctly shown at 0:00:00

Use Ctrl+Shift +; for both A2 and B2

=(B2-A2)*1440
Format General

You can then multiply by the rate per minute.

 

[Guest]

Hi Norman
thanks for the reply
that is exactly what i have been doing and it comes up with #VALUE! no matter how i format the cells.
there must be something fundamental which i am doing wrong.
sorry to be such a nuisance

 

[Fred Smith]

Are you subtracting the lower number from the higher number? Excel cannot
handle negative times. The formula (b2-a2)*1440 does work as long as b2 is
greater (ie, a later time) than a2. Could this be your problem?

 

[Norman Harker]

Hi Gerrand!

It's never a nuisance.

Are the cells you are entering the times in pre-formatted as General?
It sounds like they could be formatted as Text.

Also check that your time separator is :

Start > Settings > Control Panel > Regional settings
Look at the settings for time.

 

[Guest]

Hi Norman
You have solved my problem! Here's what I did so that other people might be able to use this information to solve the same problem:
I reset the Regional and Language Options as you suggested, as a matter of interest to (NZ) HH:mm:ss
Format A1 : B1 to Custom/ hh:mm
Format C1 to General
and use formula =(B1-A1)*1440+IF(A1>B1,1440) to cope with times which go past midnight or 00:00 hours.
Answer in minutes
Once again thank you very much for your help Norman,
by the way what does MVP stand for?

 

[Norman Harker]

Hi Gerrand!

Pleased to hear that you are sorted out now. Aussies even help out
Kiwis occasionally <vbg>

MVP is Most Valuable Professional. It's a product specific award of
Microsoft.

Three criteria:
a.. Recognized: Microsoft MVPs are acknowledged by peers and also by
Microsoft for their active participation in Microsoft technical
communities around the globe.
a.. Credible: Microsoft MVPs have demonstrated practical expertise
providing the highest quality information and content.
a.. Accessible: Microsoft MVPs are active technical community leaders
sharing their experience with peers.

See:


You'll find a lot of regular contributors here sport the award.