This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Hi,
I'm putting together a charting log to track assessments (see my screenshot below). I would like to put a formula in excel which will calculate the next due date. For example, column C should be 30 days from column B. Column D should be 30 days from column C and so on. Additionally, I would like the cell to change colors when the due date is approaching and when it becomes overdue. Any suggestions?
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Okay. I think I'm understanding it except for getting a fill color to change when the date is approaching
Try this:
Enter a name in A2, the date in B2, and use this formula in C2:
=B2+30
Leave D2 blank, but use conditional formatting on D2, using the formula option, with the formula
=AND(C2-TODAY()<30,D2="")
and choose a fill color to indicate to you that the date is approaching (you can reduce the 30 to 14, say, if you want two week notice) and the task hasn't been completed. (fill in the date in D2 when it is done.)
Then in E2, use the formula
=C2+30 (if you want to base the next due date on the previous due date)
or
=D2+30 (if you want to base the next due date on the completion date rather than the due date)
and copy D2 and paste in F2:
Then copy E2:F2 and paste in G2:H2 and then in more pairs of columns to create the size table you want.
thanks for your response!
2 things:
The formula is easy - in C2, use
=B2+30
copied to the right.
Harder is logic to say when something is overdue - you need more information in your table to indicate status other than just the due date. IF something is due tomorrow, but I finished it yesterday, there needs to be a space to enter the finished date for each of the due dates....