Forum Discussion
Need help to sort the data on date range
Let's say the first date is in B2.
In C2:
=DATEDIF(B2,TODAY(),"y")&" year(s), "&DATEDIF(B2,TODAY(),"ym")&" month(s), "&TODAY()-EDATE(B2,DATEDIF(B2,TODAY(),"m"))&" day(s)"
Fill down.
Hans with me comes an error/value with this formula if I don't enter a date.
I added the example.
But if I try the old way of the IF, the error does not appear.
Can't explain it to myself
Hi Niko,
Microsoft recommends avoiding DATEDIF with "md" as third argument; apparently it returns incorrect values in some situations. See DATEDIF function.
When I open your workbook in my English-language version of Excel, it uses EDATUM instead of EDATE and hence returns #NAME!
To avoid problems with empty dates, you can use
=IF(B2="","",DATEDIF(B2,TODAY(),"y")&" year(s), "&DATEDIF(B2,TODAY(),"ym")&" month(s), "&TODAY()-EDATE(B2,DATEDIF(B2,TODAY(),"m"))&" day(s)")
In German:
=WENN(B2="";"";DATEDIF(B2;HEUTE();"y")&" year(s), "&DATEDIF(B2;HEUTE();"ym")&" month(s), "&HEUTE()-EDATUM(B2;DATEDIF(B2;HEUTE();"m"))&" day(s)")
