How To Enter Dates & How to Know What's in a Cell?

I am trying to enter these dates 18/3/20, 6/4/20, 28/4/20, 12/5/20 and 9/6/20 and use the days(xn;x-1,n) function.

each date in its own column, all side by side in a row.

and I just keep getting errors on the function.

I am using australian date format - 6/4/20 is the 6th of april.

How can I know if my cells contain date values as far as the spreadsheet is concerned?

Am I entering the dates incorrectly?

Am I entering the days() function incorrectly?


you seem to enter your dates as text and not as real dates. To enter dates use the following least error prone procedure ( based on the dates of your question)

  • Remove all formatting from your cells
  • Enter in ISO format: 2020-03-18,2020-04-06,2020-04-28,2020-05-12 and 2020-06-09 *)
  • Now (and not before entering your dates) format the cells you want 'em to look like
  • Now you should be able to use DAYS() function to calculate the days between two dates.

*) You can use your locale format (which I don’t know). See Tools -> Options -> Language Settings -> Languages -> Option: Date acceptance patterns about how LibreOffice accepts dates in your locale.

Background (why I describe it using ISO dates):

Entering dates depends on some language settings of your LibreOffice installation which in turn determine whether LibreOffice recognizes the values entered as a data or as a text. And I’m pretty sure, that LibreOffice failed to recognize your values as date. I guess you see the values left aligned and this would be a clear indication that the dates are not real calc dates (in terms of LibreOffice real dates are integer [=numbers)) but text. In addition note: Formatting cells does not determine how you could enter dates, i.e. it doesn’t determine/influence the date recognition algorithm of LibreOffice.

Thus the answer(s) to your question: How can I know if my cells contain date values as far as the spreadsheet is concerned

  • Format the cell as number, for today (2020-02-07) it should show: 43868 (which is the 43868th day since 1899-12-30)
  • See the alignment: Number (=dates) are right aligned, while text is left aligned (if you are using default cell styles / no direct formatting)

Hope that helps.

If the answer helped to solve your problem, please click the check mark (:heavy_check_mark:) next to the answer.