date and time calculations in Excel 2003

  • Thread starter Thread starter CJ
  • Start date Start date
C

CJ

Example:
H6=01/14/2010 16:45
A6=12/31/2009 12:15

I want to get the difference between January 14th 2010 at 16:45 and December
31, 2009 at 12:15. Ideally, it would be days, hours and minutes.

Like this==> ddd:hh:mm
 
try this

=DAY(H6-A6) &" " & TEXT(MINUTE(H6-A6),"00") & ":" & TEXT(SECOND(H6-A6),"00")

the formula will return
14 30:00

hope this works

regards
elMedex
 
hi
if the difference is under 30 days(1 month) you can just subtract the newest
time from the oldest time.
in this case it would be =H6-A6.
format as dd:hh:mm which would equal 14:04:30
if the difference is greater than 30 days(1 month) use this formula....
=INT(H6-A6)&":"&TEXT((H6-A6)-INT(H6-A6),"hh:mm")
reason is that in excel, when adding, hours and minutes can be made to
accumilate ie greater than 24 hour or greater than 60 minutes. but not days.
days will roll over with the month and reset to 1. in the background the days
will be there but they just wont display in the cell properly.
the second formula will produce the same time as the first.

note: the second formula will be recogonize by excel as text, the first as a
number.

Regards
FSt1
 
Back
Top