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!

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.

  1. Activate a cell in Excel.
  2. Write the TODAY function as follows:

= TODAY ()

Writing the TODAY function

Yes, that’s it. The TODAY function takes no arguments😎

  1. And Hit Enter.

= TODAY ()

TODAY function highlight today's date

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😵

 TODAY now functions to return a serial 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.

  1. Select the cell containing the serial number.
  2. Go to the Home tab > Number Formats.
Number formats
  1. Choose the ‘Short Date’ format here. Could be a long date too.
Applying custom date format

And the results would now show a valid date!

formula cell's format

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.

Sales and dates in Excel

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.

  1. Write the TODAY function as follows.

= TODAY ()

TODAY function insert today's date

This gives us the date today i.e. 25 March 2023.

  1. Deduct 30 from it. That’s because we want to add up the sales from the last 30 days only.

= TODAY () – 30

TODAY less than 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💪

  1. Now write the SUMIF function as follows:

= SUMIF (A2:A7

Range for SUMIF

As the first argument, we have defined the range that contains the dates i.e. A2:A7.

  1. Define the criteria using a greater than operator and the TODAY function above.

= SUMIF (A2:A7, “>” & TODAY () – 30,

Criteria for SUMIF

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“?

  1. 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)

Sum range
  1. Hit Enter to get the results👇
SUMIF 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📜

Orders and static date in Excel

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:

  1. Write the TODAY function as follows.

= TODAY ()

TODAY function calculated date

This gives us the date today i.e. 25 March 2023.

  1. 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

Previous date or future date formula

We get a date 30 days before 25 March 2023📝

  1. Now write the COUNTIF function as follows:

= COUNTIF (

The COUNTIF function
  1. Define the range as the first argument

= COUNTIF (A2:A7

Range of the COUNTIF

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.

  1. Define the criteria using a greater than operator and the TODAY function above.

= COUNTIF (A2:A7, “>” & TODAY () – 30)

Criteria for COUNTIF date function

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.

  1. Hit Enter to get the results.
COUNTIF results static value

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.

Hop on here to enroll in my 30-minute free email course to learn these (and many more) Excel functions now.

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.