Time Calculations

J

Jai_Friday

How can you calculate times??

Initially looking through the help file it shows the following function

=TEXT(H1-I1,"h:mm")

which calculates the diffence in hours and minutes between two times, however

if the values are 00:09:00 and 00:10:00 it won't calculate it give a #VALUE
if the values are 00:10:00 and 00:09:00 it will calculate

What am I doing wrong

Thanks in advance

Jai
 
J

JE McGimpsey

IF you're using the WinXL default 1900 date system, you can't display
negative times, so TEXT() returns the #VALUE! error.

If you use the 1904 date system (Tools/Options/Calculation...) you can
display negative times using TEXT().

NOTE that TEXT() is not necessary, you can use

=H1-I1

by itself, then use

Format/Cell/Number/Custom h:mm

to display the result in the way you want.
 
R

Rob Hick

Dates and times in excel are stored as numbers - the date part being an
integer and the time part being a decimal (enter a date and time in
excel and the n change the formatting to number ot see an example).

The TEXT function simply converts a number to a string based on the
format you supply. So what your function is actually doing is simply
taking one number from another and then returning the result as a
string formatted as h:mm.

When you put the earlier time before the later time, the result will be
negative, since the decimal increases through the day (0.25 = 06:00,
0.5 = 12:00 etc). So the reason it returns an error is because you are
asking it to format a negative number as h:mm, which
as far as it is concerned is nonsense.

So the trick is to return the ABSOLUTE difference between the times,
then it won't matter whether you put the earlier time first of second
(assuming that is what you want to do). You can acheive this using the
ABS function, e.g.:

=TEXT(ABS(H1-I1),"h:mm")

Personally, I think that format is a bit confusing so I'd play around
with something like:

=TEXT(ABS(H1-I1),"h ""hours"", m ""minutes""")

The double quotes allow you to enter whatever text you like.

Rob
 
J

JE McGimpsey

Note that it's only nonsense using the 1900 Date system. It works fine
in the 1904 system...
 
R

Rob Hick

JE said:
Note that it's only nonsense using the 1900 Date system. It works fine
in the 1904 system...
Click to expand...

I didn't realise that - thanks. I still prefer the solution where it
doesn't matter which setting is chosen; changing settings to make
things work has a tendency to come round later and bite you on the ass!
 
Top