Dates are one of the main sources of confusion in spreadsheets. A column that looks full of dates refuses to sort properly, a subtraction returns a five-digit number, a file opened on another computer shows 3 April instead of 4 March. Almost all these problems disappear once you understand how the program represents dates internally.
Dates are numbers
To a spreadsheet a date is a serial number: the number of days elapsed since an origin. In Excel for Windows day 1 is 1 January 1900; LibreOffice Calc and Google Sheets use 30 December 1899 as day zero, which means that for modern dates the numbers match Excel's. The format you see (dd/mm/yyyy, "4 March 2026") is just a coat applied to that number.
Times are the decimal part: noon is 0.5, 6 pm is 0.75. A date with a time is therefore a number like 46085.75.
This has a few practical consequences:
- subtracting two dates returns the days between them;
- adding 30 to a date returns the date 30 days later;
- if a subtraction shows a "strange date" such as 17/01/1900, the cell has inherited the date format: change it to Number and you will see the days.
How to tell whether a date is real
A quick test: a real date, without any special alignment, sits on the right of the cell like numbers do; a date stored as text sits on the left. More rigorously, =ISNUMBER(A2) returns TRUE only for real dates.
Text dates almost always come from imports or from copying and pasting out of other programs. You convert them with DATEVALUE, if the text format matches the regional settings, or by taking the text apart:
=DATE(RIGHT(A2,4), MID(A2,4,2), LEFT(A2,2))
which turns the text "04/03/2026" into the matching day-first date regardless of the computer's settings.
Building and splitting dates
=TODAY()returns today's date,=NOW()date and time; both update at every recalculation.=DATE(year, month, day)builds a date from its parts and is the safest way to write a date inside a formula.YEAR,MONTH,DAYextract the parts;WEEKDAYreturns the day of the week as a number.
DATE handles overflows by itself: =DATE(2026, 13, 1) returns 1 January 2027 and =DATE(2026, 3, 0) the last day of February 2026. This behaviour is very handy for working out due dates.
Frequent calculations
| Need | Formula |
|---|---|
| Days between two dates | =B2-A2 |
| Same date in 3 months | =EDATE(A2, 3) |
| Last day of the month | =EOMONTH(A2, 0) |
| End of the following month | =EOMONTH(A2, 1) |
| Working days between two dates | =NETWORKDAYS(A2, B2, Holidays) |
| Date after 10 working days | =WORKDAY(A2, 10, Holidays) |
| Completed years | =DATEDIF(A2, TODAY(), "y") |
A few notes:
- EDATE handles months of different lengths correctly: three months after 30 November returns 28 February (or the 29th in leap years).
- EOMONTH is the right function for invoice terms such as "60 days end of month":
=EOMONTH(A2+60, 0), if the agreement is to add the days and then go to the end of the month. Always check the interpretation agreed with the customer. - NETWORKDAYS and WORKDAY exclude Saturdays and Sundays; the third argument is a range of holidays to exclude, which you have to fill in yourself, including local holidays. There are also .INTL variants for different working weeks.
- DATEDIF does not appear in Excel's autocomplete, but it exists and is useful for ages and length of service.
Times
Adding up hours worked seems trivial until the total goes over 24. With the hh:mm format, 26 hours are shown as 02:00, because the format represents the time of day. The solution is a custom format with square brackets: [h]:mm shows 26:00.
To turn a time into decimal hours, needed for instance to multiply by an hourly rate, multiply by 24:
=(C2-B2)*24
If a shift crosses midnight, a plain subtraction returns a negative value. You fix it with =MOD(C2-B2, 1), which brings the result back within 24 hours.
The regional format problem
In Italy we write day/month/year, in the United States month/day/year. When you type a date, the program interprets it according to the system's regional settings. As long as the date stays a number there is no problem: opened on an American computer it will simply be shown in a different order. Trouble starts with dates stored as text and with CSV files, where "04/03/2026" may become 4 March or 3 April depending on who opens it.
In my work, when dates have to travel between different systems, I use the yyyy-mm-dd format (the ISO 8601 standard): it is readable, it sorts correctly even as text and it leaves no ambiguity.
In short
- Dates are serial numbers and times are fractions of a day.
- A date aligned to the left is probably text: check it with ISNUMBER.
- Use DATE() inside formulas, EDATE and EOMONTH for due dates.
- For hour totals above 24 use the
[h]:mmformat; for decimal hours multiply by 24. - To exchange dates between different systems prefer the yyyy-mm-dd format.
