Date Formulas

[kaz]

Could someone please help with a formula to work out the difference between
two dates and show how many years, months and days there are

 

[ShaneDevenshire]

Hi,

=DATEDIF(B1,B2,"y") whole years between two dates
=DATEDIF(B1,B2,"ym") whoe months between two dates excluding years
=DATEDIF(B1,B2,"md") whole days between two dates ignoring months
where B1 is the start date and B2 is the end date.

 

[kaz]

thanks heaps -

Hi,

=DATEDIF(B1,B2,"y") whole years between two dates
=DATEDIF(B1,B2,"ym") whoe months between two dates excluding years
=DATEDIF(B1,B2,"md") whole days between two dates ignoring months
where B1 is the start date and B2 is the end date.

 

[kaz]

i need it to show only days or months or years or all three depending on the
difference between the two dates i.e first date could be 01/02/08 and the
second could be 01/04/08 which would show only 2 days, but the next set of
dates could be 01/02/08 and 01/05/09 so it should show 1 year and 3 days....
are you confused because i am!!

 

[muddan madhu]

looks too long ..... try this

start date is in A1
End date is in A2


=IF((A2-A1)>360,(INT((A2-A1)/360))&"year","")&" "&IF(((A2-A1)-INT((A2-
A1)/360)*360)>30,INT(((A2-A1)-INT((A2-A1)/360)*360)/30)&" Month","")&"
"&DATEDIF(A1,A2,"d")-INT((A2-A1)/360)*360-INT(((A2-A1)-INT((A2-A1)/
360)*360)/30)*30&" days"

 

[kaz]

the formula is great but it is adding on days - could it have something to do
with 30 days in some months and 31 in others etc?

 

[Ron Rosenfeld]

i need it to show only days or months or years or all three depending on the
difference between the two dates i.e first date could be 01/02/08 and the
second could be 01/04/08 which would show only 2 days, but the next set of
dates could be 01/02/08 and 01/05/09 so it should show 1 year and 3 days....
are you confused because i am!!

Perhaps something like:

=IF(DATEDIF(A1,A2,"y")>0,DATEDIF(A1,A2,"y") & " years, ","") &
IF(DATEDIF(A1,A2,"ym") >0, DATEDIF(A1,A2,"ym") & " months, ","") &
IF(DATEDIF(A1,A2,"md")>0, DATEDIF(A1,A2,"md")& " days")
--ron

 

[muddan madhu]

slight change

=IF((A2-A1)>=365,(INT((A2-A1)/365))&"year","")&" "&IF(((A2-A1)-INT((A2-
A1)/365)*365)>30,INT(((A2-A1)-INT((A2-A1)/365)*365)/30)&"
Month","")&""&DATEDIF(A1,A2,"d")-INT((A2-A1)/365)*365-INT(((A2-A1)-
INT((A2-A1)/365)*365)/30)*30&" days"