How to Use the TODAY Function to Check for Future Dates in Excel

The TODAY function in Excel returns the current date as a serial number. You can use this function to check if a date in another cell is in the future or not. For example, you can use the TODAY function to calculate the number of days until a deadline, or to highlight the cells that contain future dates.

In this article, we will explain the basic theory of the TODAY function, the procedures to use it, and a scenario to give a detailed example with real data. We will also show you some other approaches to check for future dates in Excel.

The TODAY function has the following syntax:

=TODAY()

It takes no arguments and returns the current date as a serial number. Excel stores dates as serial numbers, where January 1, 1900 is the number 1, January 2, 1900 is the number 2, and so on. You can format the cell that contains the TODAY function to display the date in any format you want.

To check if a date in another cell is in the future or not, you can compare the value of the TODAY function with the value of the other cell using a logical operator, such as >, <, =, >=, or <=. For example, the formula =A1>TODAY() will return TRUE if the date in cell A1 is later than the current date, and FALSE otherwise.

Procedures

To use the TODAY function to check for future dates in Excel, follow these steps:

1. Enter the dates that you want to check in a column or a range of cells. For example, enter the dates in column A from A1 to A10.
2. In another column or a range of cells, enter the formula =TODAY() to get the current date. For example, enter the formula in column B from B1 to B10.
3. In the same column or a range of cells, format the cells to display the date in your preferred format. For example, format the cells in column B to show the date as mm/dd/yyyy.
4. In another column or a range of cells, enter the formula =A1>TODAY() to check if the date in cell A1 is in the future or not. Drag the formula down to fill the rest of the cells. For example, enter the formula in column C from C1 to C10.
5. The formula will return TRUE if the date in column A is later than the date in column B, and FALSE otherwise. You can use conditional formatting to highlight the cells that contain TRUE values, or filter the data to show only the future dates.

Scenario

Suppose you have a list of tasks and their due dates in an Excel worksheet. You want to use the TODAY function to check which tasks are due in the future, and how many days are left until the deadline. You also want to highlight the tasks that are overdue or due today in red, and the tasks that are due in the next 7 days in yellow.

Here is an example of the data:

To use the TODAY function to check for future dates, follow these steps:

1. In cell B1, enter the formula =TODAY() to get the current date. Format the cell to show the date as mm/dd/yyyy. The current date is 2/9/2024.
2. In cell C1, enter the formula =B1-A1 to calculate the number of days until the due date of the first task. Drag the formula down to fill the rest of the cells in column C. The formula will return a negative value if the task is overdue, zero if the task is due today, and a positive value if the task is due in the future.
3. In cell D1, enter the formula =A1>TODAY() to check if the due date of the first task is in the future or not. Drag the formula down to fill the rest of the cells in column D. The formula will return TRUE if the task is due in the future, and FALSE otherwise.
4. Select the cells from A1 to D10, and go to the Home tab. Click on Conditional Formatting, and select New Rule. Choose Use a formula to determine which cells to format, and enter the formula =C1<=0 in the Format values where this formula is true box. Click on Format, and choose a red fill color. Click OK to apply the formatting. This will highlight the tasks that are overdue or due today in red.
5. Repeat the same steps to create another conditional formatting rule, but use the formula =AND(C1>0,C1<=7) and choose a yellow fill color. This will highlight the tasks that are due in the next 7 days in yellow.

Here is the result of the scenario:

Other Approaches

There are some other ways to check for future dates in Excel, such as:

• Using the DATE function to create a specific date and compare it with another date. For example, the formula =A1>DATE(2024,2,28) will return TRUE if the date in cell A1 is later than February 28, 2024, and FALSE otherwise.
• Using the EDATE function to add or subtract a number of months from a date and compare it with another date. For example, the formula =A1>EDATE(TODAY(),6) will return TRUE if the date in cell A1 is later than six months from the current date, and FALSE otherwise.
• Using the EOMONTH function to get the last day of a month and compare it with another date. For example, the formula =A1>EOMONTH(TODAY(),0) will return TRUE if the date in cell A1 is later than the last day of the current month, and FALSE otherwise.