TODAY Function

What is the TODAY Function?

The TODAY function in Excel returns today’s date in Excel. For example, if the date today is 4th July 2023, then Excel returns the date value in mm/dd/yyyy format as 4/7/2023.

Most of us are required to input dates in Excel, whether it is to prepare the sales summary or create a client’s deliverable file. In such files, there could be infinite situations where you might need to add today’s date. One such scenario concerns the changes you made in the file ‘today.’

In such cases, the TODAY function becomes a great tool to immediately add the date and move on to the next important tasks. Although the function might not look that significant, every single second of time saved could be spent wisely somewhere else.

There does exist a con to using the TODAY function, which we will discuss later but remember, 

keeping this function in your arsenal would benefit you more than not looking at it.

In this article, we will see the TODAY function, the drawback of using it in the spreadsheet, and a couple of examples to understand it better.

Understanding The TODAY Function

TODAY is categorized as a Date and Time function, which, if used in the spreadsheet cell, will return the current date for the user.

The function captures the computer’s current date and returns it to the spreadsheet accordingly.

First, let’s talk about the drawbacks. For example, if you use the function in a workbook, say the date is 4th August 2023, and open the same workbook tomorrow, you will find that the date has changed to 5th August 2023.

The TODAY function is extremely volatile, meaning that its value changes every other day. This makes it less usable since ‘sometimes’ you don’t want the values to change in Excel.

TODAY Function Formula

Let’s understand the syntax for the Today function, which is:

=TODAY()

My bad. There isn’t much to look for in the syntax. You don’t need to reference any cells or type in values.

All you need to do is begin with an equal sign, type the function’s name in plain text, and then use the parentheses. And yes, smash that Enter key while ensuring you don’t break the keyboard.

When writing this article, since I am using the formula in cell =TODAY() in cell B3, the result would be 7th April 2023. Obviously, this would change depending on when you read the article and try using the function on your end.

Now, if I open the same file tomorrow, the date would change to 8th April 2023; the day after that, it would be 9th April 2023.

In this case, you can just copy the value and paste it as the value so it does not change automatically. To be precise, Ctrl + C in the B3 cell, press the Ctrl + Alt + V, then paste it as ‘Value’ in the same cell.

This should do the trick for you.

But wait, what if you use the TODAY function and open the workbook, yet the date does not change?

This means that automatic calculations are set to ‘Manual’ in the workbook, and you might need to press the F9 key to update the calculations in the spreadsheet.

Excel Date Input and Formatting Techniques

Let's look at some of the Excel date input and formatting techniques below are:

1. Adding Today’s Date as a Static Value

There’s another way to add the current date in Excel; the good thing is that it won’t automatically update the next time you open the spreadsheet.

It’s not a formula or a combination of functions but just a couple of keys on your keyboard that will return the date.

All you need to do is press the Ctrl + semicolon key (;) on the keyboard, and it will return the current static date in the selected cell. So, for example, when we press the keys, we get the result:

Same result but a different method that does not bring unnecessary complications. The current date gets hard coded in the selected cell and does not change when you open the file next time.

2. Changing the Date Formats

We know how to return the current date, but the next question is - do we need the date in mm/dd/yyyy format?

If the answer is no, then you can select the cell and press the Ctrl + 1 key to open the dialog box as illustrated below:

From the ‘type’ list, you can select all the different date formats per the requirement or even input a custom format in the ‘custom’ category.

For example, if we select the long form of the date, the result would look as illustrated below:

Examples of the TODAY Function

Previously, we saw a really simple example of how to use the function to return the current date.

In this section, we will delve deeper into understanding the use cases of the function in the spreadsheet.

1. Finding the number of days between the ‘x’ and current dates

Suppose you need to complete the project assigned by the clients on 1st May 2023. If the current date is 8th April 2023, how many days exist between the two dates?

This can be easily identified by subtracting the two dates, giving the difference as an integer value.

Suppose the data looks as illustrated below:

Here, we will use the formula =C3-TODAY() in cell C5, which gives the result as

Our result is still in the date format, which you can change using the keyboard shortcut of Alt + H + N and selecting General from the drop-down menu, which will give the number of days as 23.

Similarly, you can also check for the number of days for the date in the past. For example, suppose you last ate your cheat meal on 14th January 2023. How many days has it been since the cheat meal?

The data looks as illustrated below:

Again, we will use the formula =TODAY()-C3 in cell C5, which gives the result:

Apart from the slight adjustment in the formula, nothing changes. Even the slight adjustment is just to accommodate the dates so that we don't get a negative integer as a result.

2. Finding a date in the past or future

In the previous example, we tried to find how many days existed between the two mentioned dates. This time, we will try to find the date in the past or present from the current date based on the condition that we know the number of days.

As you might have already understood, we will tweak the formula a bit so that it returns the date in the past or future.

Suppose we have the data as illustrated below:

To calculate the date in the past, we will use the formula =TODAY()-C2, which gives the date as 15th March 2023. Similarly, to find the date in the future, we will use the formula =TODAY()+C2, which gives the result:

On the other hand, the date in the future will be 2nd May 2023, which is 24 days away from the current date of 8th April 2023.

3. Eliminating the weekends from the final result

In real-life scenarios, most of us have a five working week day. Therefore, if we are to have client deliverables, it doesn’t make sense to include the weekend, i.e., Saturday and Sunday, in the deadline calculations.

So what should we do so that the weekends are excluded, and we can extract the correct number of days until the project needs to be delivered?

In this case, we will use the WORKDAY function that easily lets us exclude the weekends in finding the particular date.

Suppose the data looks as illustrated below:

To calculate the deadline date from today, excluding the weekends, we will use the formula =WORKDAY(TODAY(),C3), which gives the result:

Thus, our deadline day to submit the project is on 28th April 2023, excluding the weekends, i.e., Saturday and Sunday, fifteen days from 8th April 2023.

Researched and Authored by Akash Bagul | Linkedin

Reviewed and edited by Parul Gupta | LinkedIn

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• TRUNC Function
• TYPE Function
• UNICHAR Function
• UPPER Function
• VALUE Function