Date formula problems - Excel 2003

**lembi2001** · 04-14-2009, 08:38 AM

Hello All

I will try to make this problem as simple as i can to understand but bear with me.

I have a calculation in excel to work out how much time has passed between two sets of dates and times. my dates and times are in the same cell and are set out like this

dd/mm/yyyy hh:mm

this works ok however when i come to display the result of two dates and times months apart i get an odd result. for example:

difference between
25/01/2009 09:00
and
28/03/2009 10:00
gives the result

02  03  10:00  (using the format dd  mm  hh:mm)

this is obviously wrong as there is only 2 months difference between these dates and more than 2 days.

this also applies for dates in the same month where the month value is 1.

if i try to subtract 1 from the reult it subtracts from the day only. anyone got any ides as to what i can do to resolve this? (i could split the cells into date and time but that will make things further complicated.)

Thanks

**martindwilson** · 04-14-2009, 08:50 AM

use date.diff
=DATE.DIFF(A1,A2,3) where a1 earliest date a2 latest date the 3 is to display in ymd
but i think its part of the more functions addin.!
there is the hidden function(well i cant see it in excel2003)
of DATEDIF (notice! no full stop )
see here
http://www.cpearson.com/excel/datedif.aspx
or
http://www.meadinkent.co.uk/xl_birthday.htm
for how to use it

**daddylonglegs** · 04-14-2009, 08:56 AM

d and m formats are really designed for calendar months so m is always at least 1 (and won't exceed 12 even if your dates are more than a year apart). You can use this formula to get months, days and hours/minutes

=DATEDIF(A2,B2-(MOD(B2,1)<MOD(A2,1)),"m")&" months "&DATEDIF(A2,B2-(MOD(B2,1)<MOD(A2,1)),"md")&" days "&TEXT(B2-A2,"hh:mm")

assuming start time/date in A2 and end time/date in B2

**lembi2001** · 04-14-2009, 11:46 AM

works a treat. thanks.

Sorry to be a pest but how can i get the average time from this now? i have a number of fields containing this date difference but i need to get the average, max and min from them.

Thanks