Excel Date and Text Functions
Introduction to Excel Date Functions
How Excel Thinks About Dates
To a spreadsheet, a date isn't just text like "January 1, 2025." It's actually a number. Excel stores dates as sequential serial numbers, which allows you to perform calculations with them, like finding the number of days between two dates.
The clock starts on January 1, 1900, which is serial number 1. January 2, 1900, is 2, and so on. A more recent date like June 1, 2024, is stored as the number 45443, because it's 45,443 days after the starting point.
This system is powerful. Because dates are just numbers, you can add, subtract, and compare them easily. The way a date is displayed—like "6/1/2024" or "1-Jun-2024"—is just formatting applied on top of this underlying serial number. You can change the format without changing the date's value.
Generating Dates
Excel has several built-in functions to work with dates. Some grab the current date and time, while others help you build a date from scratch.
Function
noun
A predefined formula that performs a specific calculation using arguments in a particular order.
The TODAY function is one of the simplest. It returns the current date’s serial number and updates every time you open or recalculate the worksheet. It takes no arguments, so you just need to include the empty parentheses.
=TODAY()
If you need the current date and time, use the NOW function. Like TODAY, it's dynamic and recalculates automatically.
=NOW()
Sometimes you need to construct a specific date from separate year, month, and day values. This is common when your data is split across different columns. The DATE function handles this perfectly.
=DATE(year, month, day)
For example,
=DATE(2025, 7, 20)will return the serial number for July 20, 2025.
Breaking Dates Apart
Just as you can build dates, you can also extract their individual components. The YEAR, MONTH, and DAY functions do exactly what their names suggest. They each take a single argument: a date or a cell reference containing a date.
| Function | Description | Example (for date 12/25/2024) |
|---|---|---|
YEAR(date) | Extracts the year from a date. | =YEAR("12/25/2024") returns 2024. |
MONTH(date) | Extracts the month (1-12) from a date. | =MONTH("12/25/2024") returns 12. |
DAY(date) | Extracts the day of the month (1-31). | =DAY("12/25/2024") returns 25. |
These functions are incredibly useful for organizing and filtering data. For instance, you could use the YEAR function to group all your sales records by the year they occurred, or use the MONTH function to analyze monthly trends.
Ready to test your knowledge? Let's try a few questions.
How do spreadsheets like Excel internally store date values?
Which function returns the current date and time, and updates automatically whenever the worksheet recalculates?
Mastering these basic functions is the first step toward handling any date-related task in Excel.