Today’s Date in Excel: Function and Formula Examples (2024)
The TODAY function of Excel returns the current date (the date today)📅
But that’s not it. It can help so many of your other Excel jobs when nested into various other functions. How? Let’s learn more about that in the guide below.
Here’s the free sample workbook for this guide – download it now and dive in straight!
Table of Contents
Find today’s date with the TODAY function
So, you got this employee
The TODAY function comes from the Date and Time function library of Excel. It returns the date today.
It is a volatile function that returns the current date and updates every time you reopen or recalculate your workbook🏃♀️
Finding a date using the TODAY function will take you by a surprise. Check this out here.
- Activate a cell in Excel.
- Write the TODAY function as follows:
= TODAY ()
Yes, that’s it. The TODAY function takes no arguments😎
- And Hit Enter.
= TODAY ()
Excel returns the date today. This date information comes from the clock of your system so it will adhere to the regional settings of your PC.
Umm, but wait! My results don’t look like the above. Instead, they look like a weird number😵
If that’s you – don’t worry.
There’s nothing wrong with the function. You’re only using the wrong format.
Excel stores dates as serial numbers and that is what the TODAY function has returned🚩
You only need to format this serial number as a date.
- Select the cell containing the serial number.
- Go to the Home tab > Number Formats.
- Choose the ‘Short Date’ format here. Could be a long date too.
And the results would now show a valid date!
TODAY formula example
The TODAY function can become incredibly useful when nested into other functions. Like the SUMIF and the COUNTIF functions💡
Let me show examples of how you can pair the TODAY function with other functions in Excel.
TODAY and SUMIF: Total sales for last month
Check out some sales listed in the image below along with their dates.
Let’s say we are standing today on 25 March 2023, and we want to sum the sales for the last 30 days only. Here’s how you can do it.
- Write the TODAY function as follows.
= TODAY ()
This gives us the date today i.e. 25 March 2023.
- Deduct 30 from it. That’s because we want to add up the sales from the last 30 days only.
= TODAY () – 30
Note how Excel returns a 30-day older date i.e. 23 February 2023👀
Pro Tip!
Why did we deduct 30?
Excel stores dates as serial numbers. And the TODAY function returns a serial number representing the date today.
For example, if you turn the date “25 March 2023” into a general number, Excel will return the serial number 45010.
45010 – 30 = 44980
As 45010 less 30 is 44980, the serial number 44980 represents a 30-day older date i.e. 23 February 2023.
That’s the same as our answer above💪
- Now write the SUMIF function as follows:
= SUMIF (A2:A7
As the first argument, we have defined the range that contains the dates i.e. A2:A7.
- Define the criteria using a greater than operator and the TODAY function above.
= SUMIF (A2:A7, “>” & TODAY () – 30,
This tells Excel to see if the dates in the range A2:A7 fall within the last 30 days or not. In other words, is each date in the range A2:A7 greater than “today’s date less 30 days“?
- As the last argument, define the range to be summed up i.e. the sales in Cell B2:B7.
= SUMIF (A2:A7, “>” & TODAY () – 30, B2:B7)
- Hit Enter to get the results👇
The answer is $10,470. How has Excel calculated that?
Excel has added the sales that fall between 23 February 2023 and 25 March 2023 only. Very clearly, these are the first 3 sales and the last sale ($2000, $2300, $4500, $1670)🙈
That’s how you can nest the TODAY function into the SUMIF function to perform date-based conditional sums.
Pro Tip!
Here’s something very important for you to note.
As TODAY is a volatile function, the results for the above function will update every time you reopen the workbook or recalculate it✍
TODAY and COUNTIF: Count last month’s orders
Why only sums? Using the TODAY function together with the COUNTIF function, you can perform date-based conditional counts too.
For example, in the image below, we have multiple orders placed on different dates📜
The date today remains the same i.e. 25 March 2023, and we want to count the orders placed in the last 30 days only. To do that:
- Write the TODAY function as follows.
= TODAY ()
This gives us the date today i.e. 25 March 2023.
- Write the following formula to deduct 30 from it. That’s because we want to count the orders from the last 30 days only.
= TODAY () – 30
We get a date 30 days before 25 March 2023📝
- Now write the COUNTIF function as follows:
= COUNTIF (
- Define the range as the first argument
= COUNTIF (A2:A7
This is the range against which the criteria will be checked. We are setting it to the cell range that contains the dates i.e. A2:A7.
- Define the criteria using a greater than operator and the TODAY function above.
= COUNTIF (A2:A7, “>” & TODAY () – 30)
This tells Excel to see if each date in the range A2:A7 is greater than “today’s date less 30 days”. If it is, the COUNTIF function will count it in.
- Hit Enter to get the results.
The answer is 4. How did that come?
Excel has counted only the first 4 dates. Only these dates fall 30 days behind today i.e. between 23 February 2023 and 25 March 2023.
All orders placed on these dates meet our criteria and are counted in🧐
That’s it – Now what?
You must have enjoyed learning about the TODAY function until now. In the guide above, we learned to use the TODAY function (alone and with the SUMIF and the COUNTIF functions).
Some more handy functions that you can pair with the TODAY function to create something super useful include the VLOOKUP, and IF functions.
If you haven’t mastered these functions yet, you are missing out on something really big.
Other relevant resources
The TODAY function returns only the date.
But if you want the current time format (hour and minutes) in addition to the date, use the NOW function instead. Read our blog here to learn all about the NOW function.