An accountant wants to know when each invoice falls due, how many working days a project has left and how old the employees are. Meanwhile HR has received text from another system packed into one cell, like “Hasanova Leyla; leyla@mail.az”. In this lesson you will learn to handle such jobs in seconds with date and text functions.
How Excel stores a date
To Excel a date is a serial number counted from 1 January 1900: 1/1/1900 = 1, 27 Sep 2026 = 46292. A time is a fraction of a day: 0.5 = 12:00 noon. That is why you can subtract dates (=C2-B2 gives the days between) and add days to a date (=B2+30). To build a date from parts use =DATE(year,month,day): =DATE(2026,9,27). DATE even fixes an overflowing month: =DATE(2026,13,1) → 1 Jan 2027.
| Formula | Result | What for |
|---|---|---|
=EDATE(DATE(2026,1,31),1) | 28.02.2026 | one month later; the month end is adjusted automatically |
=EOMONTH(DATE(2026,9,12),1) | 31.10.2026 | the last day of next month — “pay by the end of next month” |
=EOMONTH(A2,-1)+1 | the 1st of A2's month | start of the month |
=NETWORKDAYS(DATE(2026,10,1),DATE(2026,10,31)) | 22 | working days without Saturdays and Sundays (both ends included) |
=NETWORKDAYS(DATE(2026,10,1),DATE(2026,10,31),H2:H3) | 21 | if H2 holds a company holiday (15 Oct 2026, a Thursday) |
=WORKDAY(DATE(2026,9,28),10) | 12.10.2026 | the date 10 working days later |
=WEEKDAY(DATE(2026,9,27),2) | 7 | day of the week; with type 2 Monday = 1, Sunday = 7 |
Ctrl+1), or you will see a number such as 46296. For other weekends there is NETWORKDAYS.INTL.Calculating age with DATEDIF
Problem
B2 holds Elvin's date of birth: 14 May 2008. Today is 27 Sep 2026. Show his exact age as “18 years 4 months 13 days”.
Show solutionHide solution
Solution
=DATEDIF(B2,TODAY(),"Y") → 18 (complete years)=DATEDIF(B2,TODAY(),"YM") → 4 (months left over after the years)=DATEDIF(B2,TODAY(),"MD") → 13 (days left over after the months)Together:
=DATEDIF(B2,TODAY(),"Y")&" years "&DATEDIF(B2,TODAY(),"YM")&" months".Check: he turned 18 on 14 May 2026, 4 more months take us to 14 Sep 2026, and 13 days to 27 Sep.
“Pay by the end of next month”
Problem
An invoice is issued on 12 Sep 2026 (a Saturday). Terms: payment by the end of next month. Find the due date, the calendar days and the number of working days the client has.
Show solutionHide solution
Solution
=EOMONTH(B2,1) → 31 Oct 2026.2) Calendar days:
=C2-B2 → 31 Oct − 12 Sep = 49 days.3) Working days:
=NETWORKDAYS(B2,C2) → 35 (both ends fall on a Saturday, so they are not counted).Check: from 14 Sep to 30 Oct there are exactly 7 full weeks, 7 · 5 = 35.
TEXT: formatting inside a formula
If you write ="Due: "&C2 you will see “Due: 46296”, because joining text drops the cell's format. TEXT converts a value to text using a format code: ="Due: "&TEXT(C2,"dd.mm.yyyy") → “Due: 01.10.2026”.
| Formula | Result (English Excel) |
|---|---|
=TEXT(46292,"dd.mm.yyyy") | 27.09.2026 |
=TEXT(46292,"dddd") | Sunday |
=TEXT(46292,"mmmm yyyy") | September 2026 |
=TEXT(1234.5,"#,##0.00") | 1,234.50 |
=TEXT(0.256,"0.0%") | 25.6% |
=TEXT(7,"000") | 007 |
Splitting and joining text
| Formula | Result |
|---|---|
=TEXTSPLIT("Hasanova Leyla"," ") | Hasanova | Leyla (spills into two cells) |
=TEXTSPLIT("Baku;Ganja;Sumgait",";") | Baku | Ganja | Sumgait |
=TEXTSPLIT(A2,,";") | the same, but down a column (splits into rows) |
=TEXTBEFORE("leyla@mail.az","@") | leyla |
=TEXTAFTER("leyla@mail.az","@") | mail.az |
=TEXTJOIN(", ",TRUE,B2:B6) | joins B2:B6 with commas and skips empty cells |
Type one example by hand
Column A holds full names such as “Hasanova Leyla”. In B2 type what the result should look like:
Leyla.Run Flash Fill
Move to B3 and press
Ctrl+E(orData › Data Tools › Flash Fill). Excel guesses the rule from your example and fills the column.Check the result
If Excel got it wrong, correct one or two more rows — Flash Fill updates the rule. Look especially at rows with double surnames or patronymics.
Interactive
Format Cells — date and number formatsCtrl+1Remember
- A date is a serial number (27 Sep 2026 = 46292): you can subtract dates and add days to them.
- EDATE adds months, EOMONTH finds the last day of a month, NETWORKDAYS and WORKDAY work with working days.
- DATEDIF with the units "Y", "YM", "MD" calculates age; it doesn't appear in autocomplete.
- TEXT turns a value into formatted text — use it only for display.
- TEXTSPLIT/TEXTBEFORE split, TEXTJOIN joins; Flash Fill (
Ctrl+E) is fast but doesn't update.