Skip to content
Educora
EN
AzərbaycancaEnglishРусскийTürkçe
My learningEducora StudioSettings
Advanced21 min18 / 27

Dates and text: professional functions

How Excel stores dates, calculating periods with EDATE, EOMONTH, NETWORKDAYS and DATEDIF, formatting with TEXT, and splitting and joining text with TEXTSPLIT, TEXTJOIN and Flash Fill.

Check yourself
In this lesson you will learn
  • Understand that a date is a serial number and build dates with DATE
  • Calculate deadlines, working days and age with EDATE, EOMONTH, NETWORKDAYS, WORKDAY and DATEDIF
  • Show numbers and dates inside text in the format you want with TEXT
  • Split and join text with TEXTSPLIT, TEXTBEFORE, TEXTJOIN and Flash Fill

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.

FormulaResultWhat for
=EDATE(DATE(2026,1,31),1)28.02.2026one month later; the month end is adjusted automatically
=EOMONTH(DATE(2026,9,12),1)31.10.2026the last day of next month — “pay by the end of next month”
=EOMONTH(A2,-1)+1the 1st of A2's monthstart of the month
=NETWORKDAYS(DATE(2026,10,1),DATE(2026,10,31))22working days without Saturdays and Sundays (both ends included)
=NETWORKDAYS(DATE(2026,10,1),DATE(2026,10,31),H2:H3)21if H2 holds a company holiday (15 Oct 2026, a Thursday)
=WORKDAY(DATE(2026,9,28),10)12.10.2026the date 10 working days later
=WEEKDAY(DATE(2026,9,27),2)7day of the week; with type 2 Monday = 1, Sunday = 7
Give the result cells a date format (Ctrl+1), or you will see a number such as 46296. For other weekends there is NETWORKDAYS.INTL.
Calculating age with DATEDIF

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 solution
DATEDIF(start, end, unit) counts complete units between two dates:
=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”

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 solution
1) Due date: =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”.

FormulaResult (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

FormulaResult
=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
TEXTSPLIT, TEXTBEFORE and TEXTAFTER exist in Microsoft 365 and Excel 2024; older versions use a combination of LEFT, MID and FIND.
  1. 1
    Type one example by hand

    Column A holds full names such as “Hasanova Leyla”. In B2 type what the result should look like: Leyla.

  2. 2
    Run Flash Fill

    Move to B3 and press Ctrl+E (or Data › Data Tools › Flash Fill). Excel guesses the rule from your example and fills the column.

  3. 3
    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
Loading simulation…
Invoices: date arithmetic and text functions in one table.
Enter today's date as a fixed valueCtrl+;
Enter the current timeCtrl+Shift+;
Flash Fill — fill by exampleCtrl+E
Format Cells — date and number formatsCtrl+1

Key points

  • 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.

Check yourself

10 questions. Every correct answer earns XP.

1 / 10
What does =EDATE(DATE(2026,1,31),1) return?