Calculate Hour difference

[Yee]

Could Excel calulate the hour difference between 16:00 and
01:00? ie, there is 9 hours difference.

If i simply do a substraction of 01:00 - 16:00 , it
returns -15. what should I tackle this problem? Many
thanks in advance.

 

[Frank Kabel]

Hi
if cell A1 holds the starting time and B1 the ending time try
=B1-A1+(B1<A1)

 

[Arvi Laanemets]

Hi

Below I assume that start time is in A1 and end time in B1.
1) When you want the result formatted in time format, then difference is
=B1-A1+(B1<A1)
formatted as "hh:mm"

2) When you want the result as number of hours, then
=(B1-A1+(B1<A1))*24
formatted as number. You may need to round the result, to get p.e. only full
hours (or use INT function)

 

[Nicky]

Hi,
if you don't want to enter times as dates (including day), then thi
formula might be what you're after, assuming your start time is in cel
A1 and end time in A2

=IF(A2<A1,1+A2-A1,A2-A1

 

[Norman Harker]

Hi Yee!

The usual solution is:

=B1-A1+(B1>A1)

Just to explain it!

Your start time of 16:00 may be formatted as hh:mm but it is recorded
by Excel as a date serial number 0.666666666667

That Date serial number can be regarded as 16:00 on the 0th day.

Your stop time of can be similarly regarded. However, if that time is
after midnight, it is a time on the 0th + 1 day.

Accordingly if after midnight we need to add 1 to the date serial
number to get what is in reality the right date serial number.

As long as our times are within any 24 hour period starting from the
start time we can regard any time less that the start time as being
after midnight.

=(B1>A1)
Returns TRUE or FALSE pending upon whether B1 is after midnight.

Within maths expressions, TRUE gets coerced to 1 and FALSE IS coerced
to 0. Accordingly, putting this expression with =B1-A1 will add 1 to
the date serial number if B1 is after midnight and add 0 if not.



--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.