Introduction
Dates are everywhere in business data: order dates, due dates, birthdays, shifts. Excel can calculate with them easily, but only once you know the trick behind them: a date is just a number with a date format. Once you see that, finding the number of days between two dates, the end of a month or the hours worked on a shift becomes simple arithmetic.
How it works
Excel stores every date as a serial number, the count of days from 1 January 1900, which is day 1. 13 November 2026 is the number 46339. Format that cell as General and you will see it. Because dates are numbers, =C2-B2 gives the number of days between two dates, and =B2+30 is the date 30 days later.
A time is stored as a fraction of a day: 0.5 is 12:00, 0.75 is 18:00. A date with a time is a whole number plus a fraction. To turn a duration into hours, multiply by 24: =(C2-B2)*24.
Useful functions:
=TODAY()returns today's date and=NOW()the date and time; both update every time the sheet recalculates.=DATE(2026,11,13)builds a date from its parts, which is safer than typing dates that depend on regional settings.YEAR,MONTHandDAYtake a date apart.=EDATE(B2,3)is the same day three months later, and=EOMONTH(B2,0)is the last day of B2's month (EOMONTH(B2,1)the end of the next month).=WEEKDAY(B2)returns 1 for Sunday up to 7 for Saturday by default;=WEEKDAY(B2,2)counts Monday as 1.=TEXT(B2,"dddd")gives the day's name.=NETWORKDAYS(B2,C2,H2:H10)counts working days from B2 to C2, both included, skipping weekends and the holidays listed in H2:H10.WORKDAYgoes the other way: the date a given number of working days later.=DATEDIF(B2,TODAY(),"y")gives complete years between two dates, such as an age. It works in every current version but is not listed in Excel's function suggestions, so you type it in full.
Time totals over 24 hours need the custom format [h]:mm; with the ordinary h:mm format, 26 hours shows as 2:00, because the whole day is hidden.
Dates typed as text, such as dates imported from another system, cannot be calculated with. They usually align left. DATEVALUE or Data, Text to Columns converts them.
Example
A shift log needs hours worked and a due date for payment:
| A | B | C | |
|---|---|---|---|
| 1 | Date | Start | End |
| 2 | 13/11/2026 | 09:00 | 17:30 |
| 3 | 14/11/2026 | 22:00 | 06:00 |
Line by line
- In D2 type
=MOD(C2-B2,1)*24and fill down: hours worked, with MOD handling the shift that ends after midnight. - In D4 type
=SUM(D2:D3)for the total hours. - In E2 type
=EOMONTH(A2,0)+15for the pay date, the 15th of the next month. - Format E2 as a date.
Expected result
D2 shows 8.5 and D3 shows 8, because MOD turns the negative difference of the night shift into 8 hours; D4 shows 16.5. E2 shows 15/12/2026: the month ends on 30 November, and 15 days later is 15 December.
In the real world
Payroll, project schedules, invoice terms such as "payment in 30 days" and staff rotas all depend on date arithmetic. Using DATE and functions such as EOMONTH, rather than typing dates, keeps a workbook working when it is opened on a computer with different regional settings.
Common mistakes
- Typing dates in another region's order, so 05/07 becomes 5 July in one country and 7 May in another.
- Subtracting times across midnight and getting a negative result; use MOD.
- Showing a 30-hour total as 6:00 because the format is h:mm instead of [h]:mm.
- Calculating with dates stored as text, which return
#VALUE!.
Best practices
- Build dates with DATE and keep each date in its own column, as a real date.
- Use TODAY in formulas that must update, and type a fixed date when the record must not change.
- Use NETWORKDAYS with a holiday list for deadlines measured in working days.
Keyboard shortcuts
| Action | Windows | Mac |
|---|---|---|
| Insert today's date | Ctrl+; | Control+; |
| Insert the current time | Ctrl+Shift+; | Command+; |
| Date format | Ctrl+Shift+# | Control+Shift+# |
| Time format | Ctrl+Shift+@ | Control+Shift+@ |
| Open Format Cells for a custom format | Ctrl+1 | Command+1 |
Summary
Dates are serial numbers counted from 1 January 1900 and times are fractions of a day, so subtracting dates gives days and multiplying a time by 24 gives hours. DATE builds dates safely; EDATE, EOMONTH, WEEKDAY, NETWORKDAYS and DATEDIF answer common date questions; and [h]:mm shows time totals above 24 hours.
Quick check
Key takeaways
- A date is a whole number of days and a time is a fraction of a day, so dates can be subtracted and added.
- DATE builds a date from a year, a month and a day; YEAR, MONTH and DAY take one apart.
- Use the [h]:mm format to show time totals above 24 hours.
My notes
Write down what you want to remember from this lesson. Notes are saved on this device as you type, and appear on your dashboard.