One of the most useful date functions in Excel is TODAY(). If you’ve ever needed your spreadsheet to display the current date dynamically, this little function can save you a lot of time. In this article, I’ll dive deep into how TODAY() works in Excel and showcase the best ways to use it.
How TODAY works in Excel?
The TODAY() function is a simple yet powerful formula that returns the current date based on your system’s clock. It updates automatically every time you open the workbook or perform a recalculation.
Here’s the syntax:
=TODAY()This function does not require any arguments. Every time you enter it into a cell, it will return the current date in the format set for that cell (typically YYYY-MM-DD or DD/MM/YYYY, depending on your regional settings).
Key Benefits of Using TODAY()
The TODAY() function is incredibly useful for a variety of tasks. Here are some benefits:
- It updates automatically, ensuring the data is always relevant.
- It integrates well with other date functions for calculations.
- It eliminates the need to manually enter dates every day.
Best TODAY Examples in Excel
Now, let’s explore some practical uses of the TODAY() function.
1. Calculate the Number of Days Since a Specific Date
If you want to know how many days have passed since a certain date, subtract that date from TODAY():
=TODAY() - DATE(2023,1,1)This formula calculates the number of days that have passed since January 1, 2023.
2. Calculate Someone’s Age
To determine someone’s age based on their birthdate:
=DATEDIF(A1, TODAY(), "Y")Here, A1 should contain the birthdate. The result will be the person’s age in years.
3. Check If a Date Is in the Past
If you want to check whether a specific date has already passed, you can use this formula:
=IF(A1 < TODAY(), "Past Date", "Future Date")4. Highlight Expired Dates with Conditional Formatting
To highlight expired due dates, follow these steps:
- Select the cells containing dates.
- Go to Home > Conditional Formatting > New Rule.
- Select Use a formula to determine which cells to format.
- Enter the formula:
=A1 < TODAY() - Set a formatting style and click OK.
5. Display Today’s Date in a Custom Format
Sometimes you need today’s date in a different format. You can achieve this with TEXT():
=TEXT(TODAY(), "DD-MMM-YYYY")This will return something like 12-Jun-2024.
Table: Common TODAY() Use Cases
| Formula | Description |
|---|---|
=TODAY() |
Returns the current date. |
=TODAY() + 7 |
Returns the date one week from today. |
=TODAY() - 30 |
Returns the date 30 days ago. |
=EOMONTH(TODAY(),0) |
Returns the last day of the current month. |
Things to Keep in Mind
Before you start using TODAY(), here are some key points:
- It does not include the current time. If you need both date and time, use
NOW()instead. - It updates automatically, so historical records based on TODAY() might change over time.
- If you need a static date, use
Ctrl + ;to insert the current date manually.
Final Thoughts
The TODAY() function is a powerful tool for working with dynamic dates in Excel. Whether you need to track deadlines, calculate ages, or highlight past dates, this simple formula can do it all. Once you get the hang of it, you’ll wonder how you ever worked without it.
Other interesting article:
How NOW works in Excel? Best NOW examples