DATEDIF FUNCTION

Question

I'm trying to do a datedif function to get age and not sure what I'm doing wrong. Cell A1 (YYYY.MM.DD) Cell B1 (YYYY.MM.DD) =Datedif(A1,B1,"Y")

Answer 1

Hi Gem. I am an Excel user like you. I am working in Windows rather than on a Mac, so I don't know if there would be any differences here, but here is what I found.

I tested your example. If I type the dates in as you show them (YYYY.MM.DD) the formula does not work because Excel is not recognizing the data input as a date even if the cell is already formatted to that style. When I type the date in as a standard date format e.g. 2/9/22, the formula works and if the cell is already formatted to your referenced style, Excel will show the date formatted in that style and the formula will still work.

So, all dates must be entered in a standard form and then Excel will format them according to the format that is set for the cell.

To format the cell, go to the Format Cells>Number>Custom and in the "Type:" box enter your format: YYYY.MM.DD and click OK. Then enter the dates in the standard format and Excel will show them in your chosen format and the formula will still work.

Reply if you have additional questions or information. Please mark this reply as answered if this solves your question.

Rich~M

Answer 2

Rich, Thank you!!! That did it! I use the Basque YYYY.MM.DD to sort dates also, so it was all formatted as dates but I did not know about formatting the way you showed me! Thank you again for your time in helping me to figure this out!

Answer 3

It should work, assuming that your system date format is yyyy.mm.dd: