LibreOffice Calc · Formula troubleshooting · Intermediate
Count Dates Between Two Dates with COUNTIFS in Calc
Use COUNTIFS and DATE in LibreOffice Calc to count dates in a range, including dates with times and full calendar months.
Updated 10 October 2026 · Formula checks: testing standards
To count appointments, invoices or other records between two dates in LibreOffice Calc, COUNTIFS with numeric dates is more reliable than typing locale-dependent date strings directly into the criteria.
Prepare real date values
Enter these five dates in A2:A6 using Calc’s date entry or a DATE(year;month;day) formula:
| Cell | Date or date-time |
|---|---|
| A2 | 2026-01-01 |
| A3 | 2026-01-15 |
| A4 | 2026-01-31 |
| A5 | 2026-02-01 |
| A6 | 2025-12-31 |
For a fully reproducible demonstration you can use:
A2: =DATE(2026;1;1)
A3: =DATE(2026;1;15)
A4: =DATE(2026;1;31)
A5: =DATE(2026;2;1)
A6: =DATE(2025;12;31)
Count all of January 2026
=COUNTIFS(A2:A6;">="&DATE(2026;1;1);A2:A6;"<"&DATE(2026;2;1))
Expected result: 3. The beginning is inclusive (>=); the next month’s first day is excluded (<). This is a robust way to count all of January, including dates with times such as January 31 at 16:45.
Why not use a quoted end date with <=?
A simple <=DATE(2026;1;31) includes a date stored at midnight on January 31, but a later time that day is a larger numeric value. Excluding the start of February avoids this boundary error.
Make the month selectable
If B1 contains the actual date 2026-01-01, use:
=COUNTIFS(A2:A6;">="&B1;A2:A6;"<"&EDATE(B1;1))
Expected result: 3. EDATE advances the month. Set B1 to the first day of the month to make this pattern predictable.
Common failures
- Count is 0 even though dates look correct: check a date cell with
=ISNUMBER(A2). Imported date text may not compare as a date; see convert text dates. - Err:502 appears: verify each criteria range has the same size. See the Err:502 guide.
- Comparisons differ across computers: prefer
DATE()over ambiguous text like"01/02/2026".
Common questions
Can I count dates within a financial year? Yes. Replace the two DATE expressions with its inclusive beginning and exclusive next-day/next-period boundary.
Why are there semicolons? This guide uses Calc’s common semicolon argument separator. Check formula preferences if your installation is configured differently.
Official reference: LibreOffice Help. This tutorial focuses on LibreOffice Calc; menu names and behavior can vary by version, operating system and locale.