Date Formulas

  • Thread starter Thread starter kaz
  • Start date Start date
K

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
 
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.
 
thanks heaps - :)

ShaneDevenshire said:
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.
Click to expand...
 
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!!
 
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"
 
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?
 
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!!
Click to expand...

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
 
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"
 
Back
Top