How spreadsheets store dates, entering UK-format dates reliably, calculating days, working days, months and ages, and fixing dates stored as text.
Dates cause more spreadsheet confusion than almost anything else, usually because of how they are stored and how regional settings affect them. Once you understand the basics, calculating deadlines, ages, working days and month-ends becomes straightforward. Everything here works in Excel and Google Sheets unless noted.
How dates are stored?
Spreadsheets store dates as serial numbers: a count of days from a starting point, with the time of day as a fraction. In Excel for Windows, 1 is 1 January 1900; Google Sheets counts from 30 December 1899, so modern dates line up with Excel. Because dates are numbers, you can add and subtract them. The format only changes how the number is displayed.
A quick check: a real date is right-aligned in a cell by default; text is left-aligned.
Entering dates in UK format
Whether 03/04/2026 means 3 April or 4 March depends on your locale. In Excel this follows your computer's regional settings; in Google Sheets it is set per file under File > Settings. Set it to United Kingdom for day/month/year. To avoid ambiguity entirely, type dates as 2026-04-03 (year-month-day), which both programs recognise, or use the DATE function: =DATE(2026,4,3).
Everyday date formulas
- Today's date:
=TODAY(). With time:=NOW(). - Days between two dates:
=B2-A2, formatted as a number. Or=DAYS(B2,A2). - Add days:
=A2+30. - Add months:
=EDATE(A2,3)gives the same day three months later, handling month lengths sensibly. - Last day of the month:
=EOMONTH(A2,0); first day of next month:=EOMONTH(A2,0)+1. - Extract parts:
=DAY(A2),=MONTH(A2),=YEAR(A2),=WEEKDAY(A2,2)(Monday = 1).
Working days and bank holidays
=NETWORKDAYS(A2,B2,Holidays!A:A) counts weekdays between two dates, inclusive, excluding dates you list as holidays. =WORKDAY(A2,10,Holidays!A:A) gives the date 10 working days after A2. For UK bank holidays, GOV.UK publishes the official dates for England and Wales, Scotland and Northern Ireland, which differ; list the ones for your nation on a Holidays tab.
If your weekend is not Saturday and Sunday, use NETWORKDAYS.INTL and WORKDAY.INTL.
Calculating age or length of service
=DATEDIF(A2,TODAY(),"Y") gives complete years between a date of birth in A2 and today. Use "M" for complete months, or "YM" for months after complete years. DATEDIF works in both programs, though Excel does not list it in its function menus. Alternatively, =YEARFRAC(A2,TODAY()) gives years as a decimal.
Formatting dates
Change how a date looks without changing its value using custom formats: dd/mm/yyyy, d mmm yyyy (3 Apr 2026) or dddd (Friday). To turn a date into text for joining with other text, use =TEXT(A2,"d mmmm yyyy"). Note that TEXT produces text, which you cannot then calculate with.
Fixing dates stored as text
Imported data, such as bank CSV files, often brings dates in as text. Signs: left-aligned, and formulas return errors or zero.
- Try
=DATEVALUE(A2), which converts text that matches your locale. - In Excel, select the column and use Data > Text to Columns, choosing the date format (DMY) in the last step.
- For awkward formats, rebuild the date:
=DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2))for text like 03/04/2026. - Check the locale setting before importing; it is often the root cause.
Common mistakes
- Result shows as a date instead of a number of days: change the format to Number.
- Mixing US and UK dates in one column after an import.
- Typing dates inside formulas as text, such as
"03/04/2026", which depends on locale. Refer to a cell or use DATE.
Frequently asked questions
How do I work out someone's age on a specific date?
Replace TODAY() with the date: =DATEDIF(A2,DATE(2026,12,31),"Y") gives their age in whole years on 31 December 2026.
How do I count entries per month?
Use COUNTIFS with a date range: =COUNTIFS(A:A,">="&D1,A:A,"<="&EOMONTH(D1,0)), where D1 holds the first day of the month. The same pattern works with SUMIFS for monthly totals.
Why does my date show as 45000-something?
That is the serial number. Format the cell as a date.
Sources and further reading
- Microsoft Support: Excel functions (by category)
- Google Docs Editors Help: Google Sheets function list
- GOV.UK: UK bank holidays
- Microsoft Support: NETWORKDAYS function
Facts checked on 20 October 2026. Spotted an error? Tell us and we will correct it.