DAY Function (LibreOffice Calc)
The DAY function extracts the day-of-month component (1–31) from a date or datetime value. It is essential for reporting, grouping, validation, and constructing date logic.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✔ |
| Onlyoffice | ✔ |
| Openoffice | ✔ |
| Wps | ✔ |
| Zoho | ✔ |
What the DAY Function Does ▾
- Extracts the day of the month (1–31)
- Accepts serial numbers, real dates, or converted text
- Ignores year, month, and time components
- Works with all standard date formats
It is designed to be simple, reliable, and ideal for date‑component extraction.
Syntax ▾
DAY(date_value)
Arguments
- date_value:
A date, datetime, or serial number representing a date.
Basic Examples ▾
Extract day from a date
=DAY("2024-03-15")
Returns 15.
Extract day from a datetime
=DAY("2024-03-15 08:45")
Returns 15.
Extract day from a cell
=DAY(A1)
Extract day from TODAY()
=DAY(TODAY())
Advanced Examples ▾
Extract day from text using DATEVALUE
=DAY(DATEVALUE(A1))
Extract day from imported CSV timestamps
=DAY(DATEVALUE(LEFT(A1;10)))
Extract day from Excel serial dates imported as text
=DAY(DATE(1899;12;30)+VALUE(A1))
Determine if two dates fall on the same day-of-month
=DAY(A1)=DAY(B1)
Build a day label (e.g., “15th”)
=DAY(A1) & IF(OR(DAY(A1)=11;DAY(A1)=12;DAY(A1)=13);"th";CHOOSE(MIN(4;MOD(DAY(A1);10)+1));"st";"nd";"rd";"th"))
Extract day from YYYYMMDD text
=DAY(DATEVALUE(LEFT(A1;4) & "-" & MID(A1;5;2) & "-" & RIGHT(A1;2)))
Extract day from DDMMYYYY text
=DAY(DATEVALUE(RIGHT(A1;4) & "-" & MID(A1;3;2) & "-" & LEFT(A1;2)))
Extract day from a timestamp with timezone text
=DAY(DATEVALUE(LEFT(A1;10)))
Edge Cases and Behavior Details ▾
DAY returns an integer (1–31)
Accepts:
- Real dates
- Serial numbers
- DATEVALUE outputs
- ISO date strings
Invalid text → Err:502
DAY ignores:
- Time components
- Timezones
- Seconds/milliseconds
- Everything after the date portion
DAY of a number < 1 → Err:502
(Serial numbers must represent valid dates)
DAY of an error → error propagates
Common Errors and Fixes ▾
Err:502 — Invalid argument
Cause:
- Text not recognized as a date
- Negative serial numbers
- Non-numeric values
Fix:
- Wrap with DATEVALUE
- Clean text with TRIM or SUBSTITUTE
Wrong day due to locale parsing
Fix:
- Convert to ISO format before DATEVALUE
DAY returns unexpected value from serial numbers
Cause:
- Serial numbers represent days since epoch
Fix:
- Ensure imported values are converted properly
Best Practices ▾
- Use DAY for grouping and reporting
- Normalize text dates with DATEVALUE
- Use DAY with YEAR and MONTH to reconstruct dates
- Use DAY with TODAY() for dynamic calculations
- Use DAY with EDATE/EOMONTH for period analysis
- Use DAY for validation of imported data
DAY completes the core date‑extraction trio — perfect for reporting, grouping, validation, and building dynamic date‑driven workflows.
Related Patterns and Alternatives ▾
- Use YEAR and MONTH for other components
- Use DATE to construct dates
- Use DATEVALUE for text conversion
- Use NOW and TODAY for dynamic values
- Use EDATE and EOMONTH for month offsets
By mastering DAY and its companion functions, you can build powerful, reliable, and fully structured date‑analysis workflows in LibreOffice Calc.