Calc Date Custom Format Formula Recognition?

English
#1

I have a formula in a cell: =NETWORKDAYS($Formulas.A1,EOMONTH(A1,0),E2:E8)

This formula only works if I have the date formatted 08/13/2025 but I prefer to write my dates as 08-13-2025. Is there a way to get Calc to recognize 08-13-2025 as a valid date format and process the formula?

I’ve tried setting up user-defined formats to no avail, though if I use the date format 2025-08-13 it works. I have all my dates set up in TextExpander the way I prefer: 08-13-2025 so I’m not particularly keen to change those.

Does anyone have any suggestions to share? I’ve checked the wiki and documentation, but no solution was clear to me.

Thanks.

1 Like
#3

If you are planning to share the spreadsheet, it is worth using one of commonly accepted patterns otherwise it might fail on a different computer. It is best to use ISO format YYYY-MM-DD if sharing internationally

2 Likes
#2

Try to extend ⇒ Tools ⇒ Options ⇒ Languages and Locales ⇒ General ⇒⇒ Date Acceptance Patterns to sth like:

<existing patterns> ; D-; M-D ; M-D-Y

ps. this is only relevant how calc interprets your input, … but complete independent how you may format the cells!

1 Like
#4

Thank you for the suggestion. I tried it but it had no effect. I’m going to give it up for now I guess. I don’t understand why it’s so picky, ha ha.

#5

Works for me.
ask125107.ods (14.5 KB)

#7

IT isn’t “picky”. YOU are.
There are any number of reasons to NOT use the US style date format. This can be made worse only by the additional misuse of the delimiter specified by ISO for the global standard.
Simply use YYYY-MM-DD and everything is simple.

  • This format is recognised in all locales.
  • It is automatically converted to a numeric value if that’s needed for calculations with EOMONTH() e.g. or with subtracting to get durations.
  • It is understood globally.
#8

The settings @karolus hinted are in effect, when you type a date into a cell. I suspect your cells are either filled with strings (text, wich looks like a date for a human being, but is handled as text) or you imported a csv, where you have separate settings, as there is no standard to detect csv-contents.

Maybe share a file here or tell exactly (if you can) what Textexpander is doing where, as your line of work is not clear for me, when I read

.

Because calculations can give wrong results, if you just don’t care how 04.05.06 is read. 2006, 4th of May or 2004, 6th of May and international software has to be able to convert this. But you are the only person to identify what you put in a cell.

#6

Play with it and piece together the parts of the puzzle that you need and that are valid for your region. Remember, every computer is stupid and doesn’t know what you want.
00000_LO-CALC_is datum or is not_083434.ods (38.4 KB)

1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.