How to Add 183 Days to a Date and Apply Conditional Formatting in Excel
Question details
Calculate a follow-up date by adding 183 days when a specific condition is met, and apply red, yellow, or green conditional formatting based on how close the date is to today.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking project deadlines or follow-up tasks based on a 183-day or six-month cycle.
- Observed behavior
- Dynamically displays a future date and highlights it in red, yellow, or green depending on the time remaining compared to the current date.
Ensure that the cells containing your original dates are formatted as proper Excel date values and not stored as plain text, otherwise the date math will return errors.
Use the IF Function and Create Date-Based Conditional Formatting Rules
Use an IF formula to conditionally add 183 days to a date, and apply multiple conditional formatting rules using the TODAY() function to color-code the cell values.
By combining a simple IF statement with basic arithmetic, you can automate date calculations based on another cell's value. Applying conditional formatting with the TODAY() function will ensure the colors update automatically as time passes.
Click on cell D3 and enter the formula =IF(C3="ED",B3+183,""). This checks if cell C3 contains 'ED', and if so, adds exactly 183 days to the date located in B3.
Select cell D3, right-click, and choose 'Format Cells'. Go to the Number tab, select 'Date', and click OK. Drag the fill handle at the bottom right of the cell down to apply the formula to the rest of column D.
Select the newly calculated date range in column D. Go to the Home tab and click 'Conditional Formatting', then select 'New Rule'. Choose 'Format only cells that contain', set the first drop-down to 'Cell Value', and the second to 'less than'. Enter =TODAY() in the box, click 'Format', choose a red fill color, and click OK.
With the column D range still selected, click 'Conditional Formatting' > 'New Rule' again. Select 'Format only cells that contain', set the condition to 'between', and enter =TODAY() and =TODAY()+60. Click 'Format', choose a yellow fill, and click OK.
Add a final rule by clicking 'Conditional Formatting' > 'New Rule'. Select 'Format only cells that contain', set the condition to 'greater than', and enter =TODAY()+60. Click 'Format', select a green fill color, and click OK.
Easily Manage Dates and Formatting with WPS Spreadsheet
WPS Spreadsheet fully supports Excel functions like IF, EDATE, and TODAY, along with robust conditional formatting tools to help you track project deadlines visually.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open the worksheet containing your dates and follow-up data.
- 2. Apply the calculation formula: Select your target cell, input the =IF(C3="ED",B3+183,"") formula, and format the cell as a Date.
- 3. Set up Conditional Formatting: Highlight the date column, navigate to the Home tab, click 'Conditional Formatting' > 'New Rule', and input your TODAY() parameters to color-code the cells.
Frequently Asked Questions
Why does my date formula return a 5-digit number instead of a date?
Spreadsheet programs store dates as sequential serial numbers for calculation purposes. To view it properly, select the cell containing the 5-digit number, right-click, choose 'Format Cells', and select a 'Date' format.
What is the difference between adding 183 days and using the EDATE function?
Adding +183 directly adds a fixed number of days, which may shift the day of the month slightly depending on month lengths. The EDATE function (e.g., EDATE(cell, 6)) adds exactly 6 calendar months, so the 15th of January becomes the 15th of July.
Why isn't my conditional formatting updating when the day changes?
Conditional formatting using the TODAY() function updates based on the system date when the sheet is recalculated. If your file was left open overnight, press F9 to force a manual recalculation, or close and reopen the workbook.
