DATEDIF

[ajcameron1929]

I am using DATEDIF function to calculate an age of person in years at a specific date. Formula example: =DATEDIF(F2,"09/01/2020","y").  In my s/s, cell F2 is their birthday and reads: 24-Feb-03. The formula gives #VALUE!

thanks for any help!

[#2]

@ajcameron1929 

Which format is in F2 it doesn't matter, it's important you have date not text. You may check by =ISNUMBER(F2) in any empty cell. If TRUE when date.

In formula better to use DATE() instead of text to avoid regional settings issues.

=DATEDIF(F2,DATE(2020,9,1),"y")

Even better second date also have in cell.

[#3]

I haven't been able to replicate the problem in my Excel. What does occur to me is that your system may be comparing one true date value with a text that looks date-like.

What I'd recommend, in any event, instead of hard-coding the DATEDIF function with the date for which you want the age, put that in cell E2. Then, making sure that both E2 and F2 contain dates properly formatted, use the formula =DATEDIF(F2,E2,"y")

It's generally better not to hard-code variables into formulas; makes them far less flexible.

[#4]

@Sergei Baklan I'd be grateful if you could help me: Im trying to get a difference in days between 2 dates, e.g. date 01/01/2019 and 01/01/2020 and am using the formula =datedif(d3,q3,"d"). For some reason, instead of giving me days the answer is a date which doesn't make sense.  I'm not sure what I'm doing incorrectly. The columns d3 and q3 are formatted to standard 'date'.

[#5]

@MaCa87 Format the cell with the DATEDIF formula as General.

[#6]

@ajcameron1929 

This formula cannot give a negative number. You have your start and end reversed.

[#7]

My answer was goofy and wrong. I entered it and it did not work, now it does.

[#8]

@MaCa87 

In general formula shall work.

Format doesn't matter, actual values are important. Perhaps you may submit small file similar to above (not screenshot, excel file) to check what's wrong.

[#9]

@Hans Vogelaar thanks this fixed it, format was set to date!

[#10]

fixed it, thank you!