Jason Stallard February 2016

Date logic in Excel 2013

The following formula works from most date combinations:

=IF(INT(B2)=INT(NOW()),"Today",IF(DAYS(NOW(),B2)>365,IF(DAYS(NOW(),B2)/365.25>1,YEAR(NOW())-YEAR(B2),0)&" years, ","")&IF(IF(MONTH(NOW())-MONTH(B2)<0,MONTH(NOW())-(MONTH(B2)-11),IF(DAYS(NOW(),B2)<28,0,MONTH(NOW())-MONTH(B2)))=0,"",IF(MONTH(NOW())-MONTH(B2)<0,MONTH(NOW())-(MONTH(B2)-11),IF(DAYS(NOW(),B2)<28,0,MONTH(NOW())-MONTH(B2)))&" months and ")&IF(DAY(NOW())-DAY(B2)<0,VLOOKUP(MONTH(B2),{1,31;2,28;3,31;4,30;5,31;6,30;7,31;8,31;9,30;10,31;11,30;12,31},2)-DAY(B2)+DAY(NOW()),DAY(NOW())-DAY(B2))&" days")

Except if B2 is 16/02/2015 15:19:00 and NOW() is 08/02/2016 14:54 where the result is 20 days but should obviously be 11 months and 30 days. I'm struggling to find the hole in my logic.

Answers


Bathsheba February 2016

Your function appears to be using the Julian year length, rather than the more complicated Gregorian one. That's one reason why your function's robustness will be questionable.

Fortunately, Excel has a built-in function DATEDIF which you can use. But, for some reason, it's a hidden function! (I have absolutely no clue why).

If A1 and A2 contain the two dates, use

=DATEDIF(A1,A2,"y") & " years, " & DATEDIF(A1,A2,"ym") & " months, " & DATEDIF(A1,A2,"md") & " days"

A1 must be earlier than A2.


Tom Sharpe February 2016

Since DATEDIF(date1,date2,"md") essentially generates random numbers when

DAY(dates2)<DAY(date1)

here's a possible formula solution (where my start and end dates are in A2 and B2):-

Years

=DATEDIF(A2,B2,"y")

Months

=DATEDIF(A2,B2,"m")

Days

=IF(DAY(B2)>=DAY(A2),DAY(B2)-DAY(A2),DAY(B2)-DAY(A2)+DAY(EOMONTH(A2,0)))

Here are a few test cases:-

enter image description here


Jason Stallard February 2016

For anyone else wanting to do the same, here's my working function:

=IF(DAYS(NOW(),B2)>0,CONCATENATE(IF(DATEDIF(B2,NOW(),"y")=0,"",DATEDIF(B2,NOW(),"y")),IF(DATEDIF(B2,NOW(),"y")=0,"", " year"),IF(DATEDIF(B2,NOW(),"y")>1,"s",""),IF(AND(DATEDIF(B2,NOW(),"y")>0,DATEDIF(B2,NOW(),"ym")>0),IF(IF(AND(DATEDIF(B2,NOW(),"md")>0,OR(DATEDIF(B2,NOW(),"y")>0,DATEDIF(B2,NOW(),"ym")>0))," and ","")=""," and ",", "),""),IF(DATEDIF(B2,NOW(),"ym")=0,"",DATEDIF(B2,NOW(),"ym")),IF(DATEDIF(B2,NOW(),"ym")=0,"", " month"),IF(DATEDIF(B2,NOW(),"ym")>1,"s",""),IF(AND(DATEDIF(B2,NOW(),"md")>0,OR(DATEDIF(B2,NOW(),"y")>0,DATEDIF(B2,NOW(),"ym")>0))," and ",""),IF(DATEDIF(B2,NOW(),"md")=0,"",DATEDIF(B2,NOW(),"md")),IF(DATEDIF(B2,NOW(),"md")=0,"", " day"),IF(DATEDIF(B2,NOW(),"md")>1,"s","")),"Less than a day")

which gives a result like "1 year, 5 months and 15 days".

Post Status

Asked in February 2016
Viewed 2,990 times
Voted 13
Answered 3 times

Search




Leave an answer