Excel TODAY Function – How To Use

The TODAY function is categorized as a Date and Time function. The function returns the current date and dynamically updates the return whenever the user makes changes in the worksheet or reopens it. The function has ample applicability, especially for financial analysts.

For instance, they may use the TODAY function to calculate holding periods.

Excel TODAY Function

Syntax

The syntax of the TODAY function is as follows:

=TODAY()

Arguments:

'None' -The TODAY function does not require any arguments. The name of the function is followed by empty parentheses.

Important Characteristics of the TODAY function

  • The TODAY function is volatile. The output of the TODAY function updates automatically whenever the user reopens or changes the worksheet.
  • If your sheet doesn't update, you can use the F9 key to force the worksheet to update values. If it doesn't work, check if automatic recalculation is disabled in Formulas > Calculation Options > Automatic.
  • The TODAY function returns a valid Excel date. If your output doesn't appear like a date, check the cell's formatting.
  • If you want to enter a static value in Excel, use Ctrl + ;
  • If you want to enter today's date and the current time, use the NOW() function instead of the TODAY function.

Examples

Let's try to see some examples of the TODAY function.

Example 1 – Plain Vanilla Formula for the TODAY Function

Alright, with the basics out of our way, let's get to actually using the TODAY() function. We'll start by using the TODAY function in its simplest form to understand how it works. Here's the formula we'll use:

=TODAY()

TODAY-Function-In-Excel-Example-01

As you'd have noticed, this is barely a formula. It's just the name of the function followed by parentheses. The reason is that the function doesn't require any arguments, so most of the time while using the TODAY function, we don't need to supply any information to the function.

The function just pulls the date from your system, converts it into an Excel recognized date, and puts it into a cell.

Example 2 – Basic Operations with the TODAY Function

The TODAY function returns a valid Excel date, which means that it is a serial number that's displayed as a date. This gives us the opportunity to perform some basic operations on the TODAY function's output. For instance, adding or subtracting days to or from the output.

TODAY-Function-In-Excel-Example-02

For instance, if I wanted to calculate the date 20 days in the future, I'd use the following formula:

=TODAY() + 20

The formula returns 10/09/2021, which is 20 days into the future from today's date (09/19/2021).

Similarly, for calculating the date 20 days in the past, we can make use of the following formula:

=TODAY() - 20

The formula returns 08/30/2021, which is 20 days before today's date (09/19/2021).

Child's play, right?

Example 3 – Nesting TODAY Function into Other Functions

TODAY function's output can be relayed to several functions to perform various tasks. For instance, you can nest the TODAY function inside the EOMONTH function or the EDATE function to calculate dates into the future or past.

TODAY-Function-In-Excel-Example-03

For instance, you could use the following formula to calculate a date 1 year into the future:

=EDATE(TODAY(),12)

If you want the first day of the current month, you could use the following formula:

=EOMONTH(TODAY(),-1)+1

(Enter image for example 3)

The EDATE function returns the same day (as entered in the first argument) 'n' months (as entered in the second argument) into the future or past. In our example, we've entered today's date and entered 12 in the second argument. This instructs Excel to calculate the same date 12 months into the future.

The EOMONTH function always returns the last day of the month as computed 'n' months (as entered in the second argument) into the past or future from the date supplied in the first argument. In our case, we've entered -1, which means the EOMONTH function will calculate the last day of the previous month. In our case, the last day of the previous month is August 31, 2021. Adding simply gives us the first day of the current month.

There are countless examples of how the TODAY function could be relayed. In summary, any formula where you need to relay today's date in an argument, you can nest the TODAY function.

Example 4 – Calculating Age in Excel With DATEDIF & TODAY Function

Now, let's take things to the next level. Here we are going to compute ages using the DATEDIF and TODAY functions.

The DATEDIF function does exactly what the name suggests. It calculates the DATE+DIF (where DIF may be inferred as DIFFERENCE) and gives us the output in our preferred unit (days, months, or years).

To calculate the age of an individual we will find the difference between their Date of Birth and Today's date. And based on this logic we can have some formulas as:

=DATEDIF(A2,TODAY(),"y")&year(s)" //Age in years
=DATEDIF(A2,TODAY(),"m")&month(s)" //Age in months
=DATEDIF(A2,TODAY(),"d")&day(s)" //Age in days

TODAY-Function-In-Excel-Example-05

Here the DATEDIF function takes three arguments – the first one is the reference of cell A2 (which contains the Date of Birth), the second argument is the TODAY function while the third argument is an identifier that tells the DATEDIF function whether we want the output in terms of years, months or days.

The DATEDIF function computes the difference between the first and the second arguments and returns it based on the set unit.

Easy-peasy, isn't it?

Here is another article that enlists many other interesting ways to Calculate Age in Excel

Example 5 – TODAY Function for Data Validation

TODAY function is also commonly used when creating data validation rules.

Let's say you are building a Monthly To-Do List in Excel. The To-Do List has two columns – one for the task name and the other one for the task deadline.

There are two constraints for the task deadline:

  • The deadline should always be greater than or equal to today's date (not allowing to add past deadlines)
  • The deadline should always be within the current month. Since it is a monthly list we only want to add the tasks that can be completed within the same month.

To have these requirements in place we can build a formula that enforces both the conditions and then apply that formula as a data validation rule.

The formula that enforces both these conditions can be written as:

=AND(B3>=TODAY(),B3<=EOMONTH(TODAY(),0))

To apply data validation, select the cell range to which you want to apply the rule. Next, choose Data from the top ribbon and select Data Validation from the Data Tools group.

Data-validation-With-Today-Function-06

Select Custom from the Allow field in the Settings tab and enter the above formula in the Formula field as shown.

Data-validation-With-Today-Function-07

Once you've set up these rules, you'll notice that you can't enter a date that's not in the current month or any date before today.

Data-validation-With-Today-Function-08

If you enter a date outside the allowed range, you'll receive an error pop-up.

Clean and efficient, wouldn't you say?

TODAY Function vs NOW Function

Think of the NOW function as an extension of the TODAY function. While the TODAY function returns today's date, the NOW function goes a step further and returns today's date as well as the current time. Just like the TODAY function, the NOW function takes no arguments.

The NOW function is a volatile function as well, and when not formatted as a date, appears as a serial number with a decimal value. The decimal value is the time component converted into a fraction of a day.

TODAY-Function-In-Excel-Example-04

Inserting Today's Date Statically

You now know how to insert today's date into an Excel cell using the TODAY function. However, you also know that the TODAY function is volatile. What happens when you want to enter a static date into a cell. Well, you don't need any formulas. All you need is a shortcut key combination: Ctrl + ; (i.e., Control + Semicolon).

While we're talking about shortcuts, you can also use the Ctrl + Shift + ; (i.e., Control + Shift + Semicolon) to enter a static timestamp into a cell.

We're not done yet — you know that the NOW function is volatile too. Fortunately, there's a shortcut you can use to enter a static date and time value into the cell as well. Press "Ctrl + ; ", add a space, and then "Ctrl + Shift + ;" (i.e., use the previous two shortcuts subsequently with a space) to enter a static date as well as time.

That brings us to the end of a simple, but extremely useful Excel function. Take a few minutes to practice with the TODAY function. Once you've mastered it, you know where to find us. We'll keep your tutorial streak on with another Excel function because we promise we're not going to run out any time soon.

About Ankit Kaul

Well, I am Ankit Kaul, the founder of Excel Trick. I am a die-hard fan of Microsoft Excel and have been working with spreadsheets for the past 10+ years. My only aim is to turn you guys into 'Excel Geeks'. Check out more about me here.