DATEDIF function in LibreOffice with mm/dd/yyyy format

[salilsurendran]

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?

[RusselB]

Are your dates actually text in mm/dd/yyyy or are they numbers in the mm/dd/yyyy format?
You can use Ctrl+F8 to turn on/off Value highlighting to determine this. If you have text, then they will stay/turn black. If they are numbers, then they will turn blue.
The fact that you are getting #NAME, however, makes me wonder if the DATEDIF function (which is one I've never heard of) is valid.
If you are sure the function is valid, then reporting how your data is set up (text/numbers) will help with determining the best way to get the information into a format that the function requires.

[RusselB]

A quick look online for help information regarding this function shows that the third parameter needs to be enclosed in quotation marks.
Your post does not show the parameter in quotation marks.

[salilsurendran]

I did Ctrl + F8 and one of the date columns turned blue the other one didn't. I tried applying format by right-clicking -> format and selecting date to the column that didn't turn blue but it still remains black. I tried quotation marks also but that gives me another date like 08/26/03 instead of no. of days. =DATEDIF(B2,F2,"d") B2 = 07/07/14
, F2 = 03/02/18

[Lupp]

See also: https://ask.libreoffice.org/en/question ... yy-format/
Please always crosslink posts if you exceptionally start a topic in more than one forum.

[robleyd]

I tried applying format by right-clicking -> format and selecting date to the column that didn't turn blue but it still remains black

Changing the display format doesn't change the type of value stored in a cell. You could try using Data | Text to Columns - with Detect special numbers checked - to convert the date strings to numeric.

I'd guess that you imported these dates from somewhere - if from a CSV file make sure the Detect special numbers option is checked.

[Villeroy]

Birthday calculation. A man was born on 1974-04-17. Today is 2012-06-13.
=DATEDIF("1974-04-17";"2012-06-13";"y") yields 38. =DATEDIF("1974-04-17";"2012-06-13";"ym") yields 1. =DATEDIF("1974-04-17";"2012-06-13";"md") yields 27. So he is 38 years, 1 month and 27 days old.
=DATEDIF("1974-04-17";"2012-06-13";"m") yields 457, he has been living for 457 months.
=DATEDIF("1974-04-17";"2012-06-13";"d") yields 13937, he has been living for 13937 days.
=DATEDIF("1974-04-17";"2012-06-13";"yd") yields 57, his birthday was 57 days ago.

The LibreOffice help uses ISO strings for demonstration purposes because this is the one and only kind of string that converts unambiguously to a day number. With dates like "1/3/1999" the same formula would return different results in the USA and the rest of the world. In a real spreadsheet you use references to date cells.

DATEDIF(A1;B1;"m") returns the correct result if A1 and B1 contain day numbers (dates) regardless of any cell formatting.

[Lupp]

By the way: DATEDIF() with "d" in the third place doesn't make much sense.

If the dates actually are dates in numerical spreadsheet representation

Code: Select all

=DATEDIF(StartDate;EndDate;"d")

simply is

Code: Select all

=EndDate-StartDate

with the disadvantage that negative differences throw an error instead of being passed as the result.

[Hunter135]

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.