How to Use Excel IF Formula for Expired, Current, and Future Dates
Question details
Categorize a date range as Expired, Current, or Future depending on how it compares to the current date.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking project timelines, subscriptions, or contract statuses using spreadsheet formulas.
- Observed behavior
- Need a streamlined formula to compare a start date and an end date against today's date to automatically output specific status labels.
Make sure your start dates and end dates are formatted as actual date values in your spreadsheet, rather than plain text.
Use a Nested IF Formula with the TODAY Function
Apply a logical test comparing the start and end dates with the current date using the TODAY() function.
This solution utilizes a nested IF function to evaluate multiple conditions sequentially. It first checks if the start date is in the future. If not, it moves on to check if the end date is today or later, capturing the 'Current' status. Any remaining dates are classified as 'Expired'.
Click on the cell where you want the status label (Expired, Current, or Future) to appear.
Type the formula =IF(A1>TODAY(),"Future",IF(B1>=TODAY(),"Current","Expired")) into the formula bar. Assume cell A1 contains your Start Date and cell B1 contains your End Date.
Press Enter to see the result, then click and drag the fill handle at the bottom-right corner of the cell to apply this formula to your other data rows.
Effortlessly Manage Dates and Formulas with WPS Spreadsheet
WPS Spreadsheet fully supports Excel's IF and TODAY functions, allowing you to seamlessly categorize your dates for projects, subscriptions, and deadlines without any hassle.
- 1. Open your document: Launch WPS Spreadsheet and open the file containing your date ranges.
- 2. Verify date formatting: Highlight your Start Dates (Column A) and End Dates (Column B), right-click, choose 'Format Cells', and ensure they are formatted as 'Date'.
- 3. Input the formula: Click the destination cell and type =IF(A1>TODAY(),"Future",IF(B1>=TODAY(),"Current","Expired")).
- 4. Drag to fill: Press Enter and drag the small square at the bottom-right of the cell down to apply the calculation to your entire list.
Frequently Asked Questions
Why does my IF formula return an error instead of the date status?
This usually happens if your dates are formatted as text instead of numbers. Select your date columns, right-click, choose 'Format Cells', and select the 'Date' category.
How can I change the status labels in the formula?
You can replace 'Future', 'Current', and 'Expired' inside the quotation marks with any text you prefer, such as 'Upcoming', 'Active', and 'Past Due'.
What if I only have one deadline date instead of a start and end date?
If you only have one date cell (for example, A1), you can simplify the formula to evaluate a single column: =IF(A1>TODAY(),"Future",IF(A1=TODAY(),"Current","Expired")).
