Hello,
I can’t seem to use the datedif function in case my dates are in mm/dd/yyyy and it always returns #NAME as value. I am using datedif(A2,B2,d). If it only works with a certain format is there a way to convert my existing dates to that format?
See also DATEDIF function in LibreOffice with mm/dd/yyyy format (View topic) • Apache OpenOffice Community Forum
Please always crosslink the threads if you (exceptionally) start a topic in more than one forum.
Posting questions as wiki posts is not recommended.
You surely entered a “d” in the third position, didn’t you?
Calc would try to interpret a d without the doublequotes as a reference to a text (the controlling character) and fail if not a named reference with name d exists.
DATEDIF() doesn’t worry about formats as long as you pass dates represented by numbers to it as the first two arguments. The standard representation of dates in spreadsheets is the number of days since 1899-12-30 as day zero.
If you pass text to the function it needs to convert it. This is done as if the DATEVALUE() function is additionally applied first. For this to work correctly the used format must comply with your locale (or an applicable setting under date acceptance patterns; not recommended in this contex).
The only format recognised under any locale for dates given as texts is as specified in ISO 8601 (with delimiters). The code for numeric dates to display in this format would be “YYYY-MM-DD”. In fact this is the only reasonable date format at all.
(Please also regard Why do I often get an error (508, 504, 502 e.g.) if I paste a Calc formula from some post into my sheet?. It’s about the parameter delimiter where you should prefer the globally accepted semicolon over the comma only accepted under specific locales.)