How to Auto-Assign Due Date, (with conditional formatting)?

Anonymous
2020-03-09T22:07:14+00:00

I am creating a project tracking spreadsheet. My team will all have access, and will enter their projects' START dates in a cell.

I need the cell next to the START date to automatically fill the DUE Date, for a specified range (e.g. 2 weeks after the specified START date). Also, I would like these cells to have conditional formatting (i.e. turn RED) when the deadline has passed.

For example:

PROJECT START DATE PROJECT DUE DATE
02/04/2020 02/18/2020

Also, not necessary, but it would be extra helpful if the DUE DATE cells could be locked (i.e. nobody can change the date or delete the formula....)

Any assistance with this matter would be greatly appreciated - thank you!

Microsoft 365 and Office | Excel | For home | Windows

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.

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2020-03-09T23:30:44+00:00

    Hi

    Please, try the following

    1- I just wonder if you could insert a column for the "Duration of the Project" just in case you might have projects with different periods of completion

    2- Then your formula will be =B2+C2 and fill down

    3-For the Conditional Formatting

    a) Select all the cells you want to apply the CF

    b) and use formula    =$D2<TODAY()

    Please note,  Only Column must be locked with $, but not the row

    3- To lock DUE DATE cells, Please check this video

    https://www.youtube.com/watch?v=wrOE8fheMR0

    Hope this helps you.

    Do let us know if you need more help

    Regards

    Jeovany

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  3. Anonymous
    2020-03-13T01:22:56+00:00

    Hi,

    Have you checked the above suggestions? You are welcome to post back to let us know if they are helpful to you, your response could also help those who have the same question in this community.

    Best Regards,

    Arck

    Was this answer helpful?

    0 comments