The TODAY function in Excel returns the current date, and is updated each time you open the workbook. If the Excel workbook is saved and opened on a later date, the TODAY function will return that date; it always shows the date of when the workbook is being viewed.
The TODAY function does not include the current time. To get the current date AND time, use the NOW function.
For example, if you use the TODAY function in an Excel workbook on October 8th, 2020, then the function will return 10/8/2020. If you save the workbook and view it the next day, the function will return 10/9/2020. If you were to close the workbook for another three days and open it again, the TODAY function would return 10/12/2020.
The TODAY function does not take any arguments, but still needs the empty parenthesis so that Excel knows it's a function.
=TODAY()If today was September 24th, 2021 and you were to type =TODAY() into cell B2 of your Excel worksheet, you would see the following result:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | ||||
| 2 | 9/24/2021 | |||
| 3 |
If you were to open the workbook exactly 30 days later (on October 24th) you would see the following result:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | ||||
| 2 | 10/24/2021 | |||
| 3 |
To return dates a certain number of days in the past in Excel, simply subtract that number from the TODAY function. For example, if you wanted the date from 5 days ago, you could write =TODAY()-5. Remember, this formula updates each time you view the workbook, so it will always show the date 5 days before the date of when the worksheet is viewed.
Conversely, you can add days to get future dates in Excel. The formula =TODAY()+2 would return the date 2 days in the future from whenever the workbook is being viewed.
For example, say today was October 24, 2021. In cell B1 you type =TODAY() and in cell B2 you type =TODAY()+2. You would see the following results:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Current Date: | 10/24/2021 | ||
| 2 | Date in 2 Days: | 10/26/2021 | ||
| 3 |
In Excel you can extract the day, month, and year components from a date. =YEAR(TODAY()) will return the current year, =MONTH(TODAY()) will return the number of the current month (from 1 to 12), and =DAY(TODAY()) will return the current day of the month (from 1 to 31).
For example, if the date was March 12, 2021, then =YEAR(TODAY()) would return 2021. =MONTH(TODAY()) would return 3 (because March is the 3rd month from January to December) and =DAY(TODAY()) would return 12.
We can use the date components in Excel to find the difference between dates in days, months, and years.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Birth Date: | 10/8/1992 | ||
| 2 | ||||
| 3 |
If someone's birth date was displayed in cell B1, then the following formula would return the person's age in days because it returns the number of days between today and the date in the cell:
=TODAY()-B1But say you wanted the person's age in full years. You could write the following formula:
=YEAR(TODAY())-YEAR(B1)This would return the number of years between the date in B1 and today's date. If we were to view the Excel workbook any time in 2020, then =YEAR(TODAY()) would return 2020 and =YEAR(B1) would return 1992, so we would get a result of 28.
Say you wanted to know the number of months until the person's next birthday month. You could write the following formula, also using the IF function:
=IF(MONTH(B1) >= MONTH(TODAY()), MONTH(B1) - MONTH(TODAY()), 12-(MONTH(TODAY())-MONTH(B1)))If the month in B1 (which is 10) is greater than or equal to today's month, then subtract today's month from the month in B1. If the current month was March, then we would get 10-3, which means 7 months remain until the person's next birthday month.
However, if the current month was greater than the month in B1, say November, we would get 11-10, or -1, which wouldn't make sense. This is where the final piece of the IF function comes in. Here the function would return 12-(11-10), or 11 months until the person's next birthday month.
As mentioned before, the TODAY function will return the current date of whenever you open the workbook, which means the TODAY function will return a different value each day.
But what if you want to get today's date but don't want it to change each day? You have two options in Excel.
1. You can manually enter the date into the cell
2. You can use a keyboard shortcut to get the current date
To use the shortcut for the current date, hold Ctrl and press the ; (semicolon) key. This will enter the current date in the active cell, and this date will not change when you open the workbook in the future.