Forum Discussion
Need help to sort the data on date range
| RAJESH | 11/16/2019 |
| TAKAHIRO | 11/15/2018 |
| EVELYN MEE | 11/14/2019 |
| Rahul | 11/13/2021 |
| Saskia | 11/13/2015 |
| Andre | 11/12/2021 |
Need a formula, where to add another column where it shows how old the data is .
Example 10 days, 1 month, 3 month
3 Replies
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)")
