Highlight Excel Submission Dates Within 30 Days of Their One-Year Due Date
Question details
Highlight submission dates or return a Yes/No status when the current date is within 30 days before each submission date's one-year anniversary.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A submission date, such as April 23, 2023, has a due date one year later, and the user needs an advance reminder window before that due date.
- Observed behavior
- The desired result is a highlight or status indicator from 30 days before the one-year due date through the due date itself.
Make sure the submission-date cells contain real Excel dates rather than text. In the formulas below, replace A2 with the first submission-date cell in your selected range.
Apply conditional formatting for the 30-day one-year due-date window
Use this option to automatically highlight each submission date when today is from 30 days before its one-year anniversary through the anniversary date.
EDATE calculates the one-year anniversary by calendar month, so it handles different month lengths and leap years more reliably than adding a fixed number of days.
Select the cells that contain submission dates, such as A2:A100. Ensure that A2 is the first cell in the selected range.
On the Home tab, select Conditional Formatting > New Rule > Use a formula to determine which cells to format.
Enter =AND(TODAY()>=EDATE(A2,12)-30,TODAY()<=EDATE(A2,12)). Keep A2 relative so Excel evaluates each row's submission date.
Select Format, choose a Fill color or other formatting, and then select OK twice to save the rule.
Display Yes or No in a due-date status column
Use this option when you need a visible text status beside every submission date instead of, or in addition to, a cell highlight.
Highlight One-Year Due-Date Reminder Windows in WPS Spreadsheets
WPS Spreadsheets lets you create the same formula-based conditional-formatting rule to flag submission dates during the 30 days before their one-year due date. Open your workbook, apply the EDATE and TODAY formula to the date range, and choose a clear reminder color.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, select Spreadsheets, and open the workbook containing the submission-date column.
- 2. Select the date cells: Select the submission-date range, such as A2:A100, with A2 as the first active cell.
- 3. Add a conditional-formatting formula: Go to Home > Conditional Formatting > New Rule, choose a formula rule, and enter =AND(TODAY()>=EDATE(A2,12)-30,TODAY()<=EDATE(A2,12)).
- 4. Set the reminder style: Choose Format, set a fill color for the alert, and select OK to apply the rule to the selected range.
Frequently Asked Questions
What formula highlights a date 30 days before its one-year anniversary?
For a submission date in A2, use =AND(TODAY()>=EDATE(A2,12)-30,TODAY()<=EDATE(A2,12)) in Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
How can I show Yes or No when a one-year due date is approaching?
Enter =IF(AND(TODAY()>=EDATE(A2,12)-30,TODAY()<=EDATE(A2,12)),"Yes","No") in a status column, then fill the formula down.
Why use EDATE instead of adding 365 days to a submission date?
EDATE adds calendar months and is better suited to one-year anniversaries, including dates around February and leap years. Use =EDATE(A2,12) to calculate the due date.
Can the conditional-formatting rule include the due date itself?
Yes. The condition TODAY()<=EDATE(A2,12) includes the exact one-year due date. After that date passes, the highlight stops.
How do I highlight only overdue one-year due dates?
Create a formula-based conditional-formatting rule with =TODAY()>EDATE(A2,12). This highlights dates only after their one-year anniversary has passed.
