How to Create an Excel Follow-Up Date Formula from a Drop-Down List
Question details
The user needs to calculate specific follow-up dates using an Excel formula that adds different day intervals based on a diagnosis chosen from a drop-down list.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Automating task deadlines or patient follow-ups based on specific categories selected via a drop-down list.
- Observed behavior
- Requires a formula that dynamically adds different days (e.g., +14 for CABG, +7 for PCI) to a starting date depending on the value in another cell.
Ensure you have your base date column and drop-down list column set up correctly, and verify that the target cells are formatted as Dates rather than General or Text.
Use the IFS Function for Dynamic Follow-Up Dates
Use the IFS function to evaluate multiple conditions from your drop-down list and seamlessly add the corresponding number of days to your base date.
The IFS function checks whether one or more conditions are met and returns a value corresponding to the first true condition. It is perfect for assigning varying date intervals without using complex nested formulas.
Click on the cell where you want the follow-up date to appear (for example, cell T2).
Type the formula =IFS(I2="CABG",D2+14,I2="PCI",D2+7,TRUE,"-") into the formula bar. In this example, I2 contains the drop-down list selection and D2 contains the initial date.
Press Enter to calculate the first result. Click the bottom-right corner of cell T2 and drag the fill handle down to apply the formula to the remaining rows.
Select the entire result column, navigate to the Home tab on the ribbon, and change the Number Format dropdown to Short Date.
Use a Nested IF Function (For Older Excel Versions)
If you are using an older version of Excel that does not support the IFS function, a nested IF statement achieves the exact same result.
Calculate Dates Easily Using WPS Spreadsheet
WPS Spreadsheet fully supports advanced logical functions like IFS and IF, making it incredibly easy to automate your date calculations, schedules, and drop-down lists.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your drop-down lists and dates.
- 2. Select the output cell: Click on the blank cell where you want the calculated follow-up date to appear.
- 3. Enter the logical formula: Type your =IFS() or =IF() formula referencing your drop-down list cell and base date cell.
- 4. Format the output: Press Enter, then use the Number Format dropdown on the Home tab to ensure the cell displays as a Date.
Frequently Asked Questions
Why is my date formula returning a 5-digit number like 44210 instead of a date?
Spreadsheet software stores dates as sequential serial numbers for calculation purposes. To view it properly, select the cell, go to the Home tab, and change the Number Format from 'General' to 'Short Date'.
Can I reference a separate table for the day intervals instead of typing them directly into the formula?
Yes. You can use a VLOOKUP or XLOOKUP function paired with your base date to pull the required interval days from a dedicated reference table. For example: =D2+VLOOKUP(I2, ReferenceTable, 2, FALSE).
What happens to the formula if my drop-down list cell is left blank?
If the drop-down cell is blank, the IFS formula will evaluate the 'TRUE' catch-all condition at the end (outputting a hyphen in this example). To leave the target cell completely blank instead, you can add an initial condition like I2="","" to your formula.
