Text and date functions for advanced scenarios in Excel

Excel offers powerful Text and Date functions that are especially useful for advanced data manipulation, analysis, and formatting. Below are some key functions with advanced use cases.

Advanced Text Functions

1. TEXT

Purpose: Format numbers, dates, or other values into specific text formats.
Syntax: =TEXT(value, format_text)

Example:
If A1 contains the number 5000.25, use:

=TEXT(A1, "$#,##0.00")

Result: $5,000.25

The format #,##0.00 is a custom number format in Excel, commonly used to display numbers with specific formatting rules. Here’s a breakdown of what each symbol means:

Symbol Explanation

# (Placeholder)
- Represents optional digits.
- If there are no digits in that position, it leaves the space blank (no leading zeroes).
, (Comma Separator)
- Adds a thousands separator for every three digits.
- Example: 1234567 → 1,234,567
0 (Placeholder)
- Ensures that at least one digit is displayed in that position, even if it’s 0.
- Example: If the value is 5, it will display as 5.00 because the two 0s after the decimal point force two digits.
. (Decimal Point)
- Specifies where to place the decimal point in the number.
00 (Two Zeroes After the Decimal)
- Ensures exactly two digits are displayed after the decimal point, rounding if necessary.
- Example:
  - 123.4 → 123.40
  - 123.456 → 123.46 (rounding applied).

How it Works in Practice

Number	Format `#,##0.00`	Explanation
`1234.5`	`1,234.50`	Adds a comma, ensures 2 decimals.
`567890`	`567,890.00`	Adds a comma, shows decimals.
`12`	`12.00`	Adds two trailing zeros.
`0`	`0.00`	Displays zero with decimals.
`1234567.891`	`1,234,567.89`	Rounds to 2 decimals.

Use Cases

Financial Reports: Displaying currency or monetary values.
Data Presentation: Adding clarity by formatting numbers with commas and decimals.
Consistency: Ensuring all numbers in a report have the same number of decimal places.

How to Apply It

Select the cells you want to format.
Go to the Home tab → Number group → More Number Formats (click the small arrow).
Choose Custom and enter #,##0.00 in the field.
Click OK to apply.

Scenario: Use this function to generate formatted text for reports or dashboards.

2. LEFT, RIGHT, MID

Purpose: Extract specific parts of a text string.
Syntax:
- =LEFT(text, num_chars)
- =RIGHT(text, num_chars)
- =MID(text, start_num, num_chars)

Example:
Suppose A1 contains the string "Invoice2024".

Extract the year using RIGHT: =RIGHT(A1, 4) Result: 2024
Extract the word "Invoice" using LEFT: =LEFT(A1, 7) Result: Invoice
Extract "202" using MID: =MID(A1, 8, 3) Result: 202

Scenario: Use these functions to clean or organize data like product codes, invoice numbers, or IDs.

3. TRIM

Purpose: Remove all extra spaces from text except single spaces between words.
Syntax: =TRIM(text)

Example:
If A1 contains " John Doe ", use:

=TRIM(A1)

Result: "John Doe"

Scenario: Use this function when importing data with inconsistent spacing (e.g., from CSV files).

4. CONCATENATE / CONCAT

Purpose: Combine text strings.
Syntax:
- =CONCATENATE(text1, text2, ...)
- =CONCAT(text1, text2, ...) (preferred in newer Excel versions)

Example:
Combine first and last names from A1 (First Name) and B1 (Last Name):

=CONCAT(A1, " ", B1)

Result: "John Doe"

Scenario: Use it to create full names, merge IDs with descriptions, or prepare email addresses.

Advanced Date Functions

1. TODAY and NOW

Purpose: Return the current date or date-time.
Syntax:
- =TODAY() (Date only)
- =NOW() (Date and Time)

Example:
If today’s date is 15-Dec-2024, the results are:

=TODAY() → 15-Dec-2024
=NOW() → 15-Dec-2024 10:15 AM

Scenario: Use these for dashboards that display live date and time or calculate deadlines.

2. DATEDIF

Purpose: Calculate the difference between two dates (in days, months, or years).
Syntax: =DATEDIF(start_date, end_date, unit)
- unit can be:
  - "Y" → Years
  - "M" → Months
  - "D" → Days

Example:
To find the age of a person born on 01-Jan-1990 (in A1) and today’s date in B1:

=DATEDIF(A1, B1, "Y")

Result: 34 (if today is 15-Dec-2024)

Scenario: Use this to calculate service tenure, age, or time duration between two events.

3. TEXT for Dates

Purpose: Format dates as text.
Syntax: =TEXT(date, format_text)

Example:
If A1 contains 15-Dec-2024, use:

=TEXT(A1, "MMMM DD, YYYY")

Result: "December 15, 2024"

Scenario: Use this to create reports with custom date formats.

4. WORKDAY and NETWORKDAYS

WORKDAY: Returns a date after adding a specified number of workdays (excluding weekends/holidays).
Syntax: =WORKDAY(start_date, days, [holidays])

Example:
To calculate a project deadline starting from 15-Dec-2024 and lasting 10 workdays:

=WORKDAY(A1, 10)

Result: 30-Dec-2024 (skipping weekends).

NETWORKDAYS: Returns the number of workdays between two dates.
Syntax: =NETWORKDAYS(start_date, end_date, [holidays])

Example:
If A1 = 15-Dec-2024 and B1 = 31-Dec-2024:

=NETWORKDAYS(A1, B1)

Result: 13

Scenario: Use these to calculate project deadlines or total workdays for payroll.

5. EDATE and EOMONTH

EDATE: Adds or subtracts months from a date.
Syntax: =EDATE(start_date, months)

Example:
To calculate a due date 3 months from 15-Dec-2024 (in A1):

=EDATE(A1, 3)

Result: 15-Mar-2025

EOMONTH: Returns the last day of the month for a given date.
Syntax: =EOMONTH(start_date, months)

Example:
For the end of the month 2 months after 15-Dec-2024 (in A1):

=EOMONTH(A1, 2)

Result: 29-Feb-2025 (Leap Year)

Scenario: Use these for managing subscription periods, due dates, or month-end reports.

When to Use These Functions

Data Cleaning: Remove unwanted spaces or merge fields with TRIM and CONCATENATE.
Advanced Reports: Create customized and formatted reports with TEXT and EOMONTH.
Project Management: Calculate deadlines and durations with WORKDAY, DATEDIF, and NETWORKDAYS.
Time-Based Calculations: Use TODAY or NOW to make live dashboards or aging reports.