Time Difference

[bajjo]

HI!
I am calculating difference between two date/Time Value and then their
Absolute Value using this formula. The results are correct for some
values but if the time difference is more than 770 Hrs then wrong
results are comming.


=DAY(B2-A2)*24+HOUR(B2-A2)+MINUTE(B2-A2)/60+SECOND(B2-A2)/3600

Please refer to attached Excel file for the values,format and formulas
etc..

Thanks.

File Attached: http://www.exceltip.com/forum/attachment.php?postid=282093 (difference.xls)

 

[J.E. McGimpsey]

That's because DAY() returns a value between 1 and 31 - which gives
a maximum of 744 hours (+ 24 for the HOURS() comparison).

Since XL stores dates as integer offsets from a base date and times
as fractional days, you just have to subtract one date from the
other:

A1: 1 January 2003
A2: 10 February 2004
A3: =ABS(A2-A1) ==> 9720:00

when A3 is formatted with Format/Cells/Number/Custom [hh]:mm

(the brackets prevent hours from rolling over at 24).

BTW - posting attachments in non-binary groups is usually frowned
upon and your message would have been filtered out by many regulars
if it hadn't been made into a downloadable file.