π Excel Formulas β Part 6
This part focuses on Date & Time formulas β essential for reporting, deadlines, ageing analysis, trends, and time-based calculations.
1οΈβ£ TODAY
Returns the current date.
=TODAY()
β’ Example: Automatically display today's date.
2οΈβ£ NOW
Returns the current date and time.
=NOW()
β’ Example: Track when a workbook was last recalculated.
3οΈβ£ DATE
Creates a date from year, month, and day.
=DATE(2026,9,24)
β’ Result: 24-Sep-2026
4οΈβ£ YEAR
Extracts the year from a date.
=YEAR(A2)
β’ Example: 24-Sep-2026 β 2026
5οΈβ£ MONTH
Extracts the month number.
=MONTH(A2)
β’ Example: 24-Sep-2026 β 9
6οΈβ£ DAY
Extracts the day of the month.
=DAY(A2)
β’ Example: 24-Sep-2026 β 24
7οΈβ£ EOMONTH
Returns the last day of a month.
=EOMONTH(A2,0)
β’ If A2 is 24-Sep-2026 β Result: 30-Sep-2026
β’ You can also move between months:
β’ =EOMONTH(A2,1) β Last day of next month
8οΈβ£ DATEDIF
Calculates the difference between two dates.
=DATEDIF(A2,B2,"Y")
β’ Returns the number of complete years.
Other units:
β’ "Y" β Years
β’ "M" β Months
β’ "D" β Days
Example:
β’ =DATEDIF(A2,B2,"D") β Number of days between the dates.
9οΈβ£ DAYS
Returns the number of days between two dates.
=DAYS(B2,A2)
β’ Example:
β’ Start Date β 01-Sep-2026
β’ End Date β 24-Sep-2026
β’ Result β 23
π NETWORKDAYS
Calculates the number of working days between two dates, excluding weekends.
=NETWORKDAYS(A2,B2)
β’ You can also exclude holidays:
β’ =NETWORKDAYS(A2,B2,E2:E10)
π§ Quick Reference
β’ TODAY() β Current date
β’ NOW() β Current date + time
β’ DATE() β Create a date
β’ YEAR() β Extract year
β’ MONTH() β Extract month
β’ DAY() β Extract day
β’ EOMONTH() β Last day of month
β’ DATEDIF() β Difference between dates
β’ DAYS() β Number of days between dates
β’ NETWORKDAYS() β Working days between dates
π‘ Practice
β’ Employee β Joining Date β End Date
β’ Rahul β 10-Jan-2022 β 24-Sep-2026
β’ Priya β 15-Mar-2023 β 24-Sep-2026
β’ Amit β 20-Jul-2024 β 24-Sep-2026
β’ Neha β 05-Feb-2025 β 24-Sep-2026
β€οΈ Double Tap & React For Part 7!
π Excel Formulas β Part 5
This part focuses on Text Functions β essential for cleaning, extracting, and combining text in Excel.
1οΈβ£ LEFT
Extracts characters from the beginning of a text.
=LEFT(A2,5)
Example:
INDIA123 β INDIA
2οΈβ£ RIGHT
Extracts characters from the end of a text.
=RIGHT(A2,3)
Example:
INV123 β 123
3οΈβ£ MID
Extracts characters from the middle of a text.
=MID(A2,4,5)
Example:
EMP-12345 β 12345
4οΈβ£ LEN
Counts the number of characters in a text.
=LEN(A2)
Example:
Excel β 5
Spaces are also counted.
5οΈβ£ TRIM
Removes unnecessary spaces from text.
=TRIM(A2)
Example:
"Β JohnΒ Β SmithΒ " β "John Smith"
Very useful when cleaning imported data.
6οΈβ£ UPPER
Converts text to uppercase.
=UPPER(A2)
excel β EXCEL
7οΈβ£ LOWER
Converts text to lowercase.
=LOWER(A2)
EXCEL β excel
8οΈβ£ PROPER
Capitalizes the first letter of each word.
=PROPER(A2)
john smith β John Smith
9οΈβ£ CONCAT
Combines text from multiple cells.
=CONCAT(A2," ",B2)
Example:
A2 = John
B2 = Smith
Result β John Smith
π TEXTJOIN
Combines multiple values using a delimiter.
=TEXTJOIN(", ",TRUE,A2:A5)
Example:
SQL, Excel, Power BI, Tableau
The TRUE tells Excel to ignore empty cells.
π§ Quick Reference
LEFT β Extract from beginning
RIGHT β Extract from end
MID β Extract from middle
LEN β Count characters
TRIM β Remove extra spaces
UPPER β Convert to uppercase
LOWER β Convert to lowercase
PROPER β Capitalize words
CONCAT β Combine text
TEXTJOIN β Combine text with a separator
π‘ Practice
Suppose:
A2 = "Β john smithΒ "
Try creating formulas to:
1. Remove extra spaces β TRIM
2. Convert to uppercase β UPPER
3. Convert to lowercase β LOWER
4. Capitalize properly β PROPER
5. Count characters β LEN
β€οΈ Double Tap & React For Part 6!