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.

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:-

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".

Asked in February 2016

Viewed 2,990 times

Voted 13

Answered 3 times

Viewed 2,990 times

Voted 13

Answered 3 times