Recently I’ve come across a question by someone who wants to track how long til their projects are due. So I decided to write a post about it. We want to create a tracking spreadsheet to keep track of projects that are on going. We want to record the start date of the project, the estimated number of days to complete, the estimated completion date and the number of days until the due date. It will look like this below:
Project Start Date – This date is manually entered. It is just the date the project starts.
Estimated Days to complete – Here you enter the number of days assigned to the project.
Estimated due date – This is a formula to calculate the date the project is due. We basically add the Project Start Date plus the Estimated Days to Complete. In this example, it is =B5+C5
Days Left until due – This is a countdown until the Estimated due date. We take the Estimated due date and subtract out our current date. The formula for this example is =D5 – NOW() .
So this will give you the countdown in days in column E. However, we also want to format that cell a certain color depending on the number of days left. We want the cell to be Yellow if there are between 7 and 14 days to go and Red if there are less than 7 days to go.
To do this, we put our cursor on the cell and go to :
Home—Conditional Formatting — Hilight Cellls —-Between
Home—Conditional Formatting — Hilight Cellls —-Less Than
Now you have your tracking spreadsheet and are ready to keep track of your projects!