Text and Date Functions
Taking text apart
| Function | Returns | Example with "LAP-2025-IN" |
|---|---|---|
LEFT(text, n) |
first n characters | =LEFT(A2,3) gives LAP |
RIGHT(text, n) |
last n characters | =RIGHT(A2,2) gives IN |
MID(text, start, n) |
n characters from a position | =MID(A2,5,4) gives 2025 |
LEN(text) |
number of characters | =LEN(A2) gives 11 |
FIND(find, text) |
position of one text inside another | =FIND("-",A2) gives 4 |
Combine them when the length varies. This returns everything before the first hyphen:
=LEFT(A2,FIND("-",A2)-1)Microsoft 365 adds simpler functions for the same job:
=TEXTBEFORE(A2,"-")
=TEXTAFTER(A2,"-")
=TEXTSPLIT(A2,"-")Putting text together
=[@Rep]&" ("&[@Region]&")"
=TEXTJOIN(", ",TRUE,Reps[Rep])TEXTJOIN takes a delimiter, a TRUE or FALSE for ignoring empty cells, and the values to join.
Formatting a number or date as text
TEXT converts a value to text in a format you choose:
=TEXT([@Date],"mmm yyyy")
=TEXT([@Revenue],"#,##0")
="Total: "&TEXT(SUM(Sales[Revenue]),"#,##0")The result is text. Use it for labels, not for further calculation.
Parts of a date
=YEAR([@Date])
=MONTH([@Date])
=DAY([@Date])
=WEEKDAY([@Date])WEEKDAY returns 1 for Sunday through 7 for Saturday by default.
Calculating with dates
Because a date is a number, subtracting two dates gives the days between them.
| Formula | Result |
|---|---|
=TODAY() |
today’s date, updated each time the workbook recalculates |
=TODAY()-[@Date] |
days since the order |
=EOMONTH([@Date],0) |
last day of the order’s month |
=EOMONTH([@Date],-1)+1 |
first day of the order’s month |
=EDATE([@Date],3) |
the same day three months later |
=NETWORKDAYS(A2,B2) |
working days between two dates, excluding weekends |
=DATEDIF(A2,B2,"Y") |
complete years between two dates |
DATEDIF also accepts "M" for complete months and "D" for days.
A month column for reporting
Reports are usually grouped by month. Add a Month column that holds the first day of each order’s month:
=EOMONTH([@Date],-1)+1Format it as mmm yyyy. It stays a real date, so it sorts in calendar order, which a text label such as “Feb 2025” would not.
Practice
Create an order label such as 1001-Asha-Jan from the OrderID, Rep and Date columns.