I recently created a project management dashboard from Mynda's excellent YouTube tutorial and now I'm requesting some help from the forum.
As you know, with all projects there are delays, so what I'm trying to add to the "Workings" and "Dashboard" worksheets is a tracker for showing these delays. I have added a column for "Current Date" and "Days Delayed" (calculated from original "End Date" and "Current Date"), so now I am trying to format the date rows to show "red" when a task is late. I have tried several Conditional Formatting formulae, but to no avail. Do you have any suggestions?
I have the project beginning on 01-Jan and running through 26-Jun.
Here is the formula I have used, but only the first cell (01-Jan) is red:
=AND(J$6>$F6,WORKDAY.INTL($F6,$H6,1)-1>L$5)
I have added a picture to show the column headers (and letters) and row numbers.
Thank you!
Hi Gene,
Welcome to our forum! You haven't said which cells you want the format applied to. e.g. the whole row, the date cells relating to the project, the entire date cells etc. It's difficult to give you an answer without this information.
Please watch this tutorial that explains how conditional formatting using formulas works. Hopefully this will help you solve the problem.
If you're still stuck, please come back with your latest formula attempt and a sample file (preferred) or screenshots of the conditional formatting rule and spreadsheet big enough that we can easily read them.
Mynda
Hi Mynda,
Thank you for your quick response. I want to format the rows under the date headings similar to your pink and blue rows (Gantt chart format), but with red indicating that the project has gone beyond the original end date.
Thank you,
Gene
Hi Gene,
It's still not clear if you want to highlight up to the current date or the original end date of the project, and as you didn't provide a file as requested I've had to re-create your data (from an image I can barely read as it's too small). That said, I think this formula will work assuming you only want to highlight up to the original end date:
=AND($H6=0,$J6>$F6,$E6<=L$5,$F6>=L$5)
In future, please help us out by creating a mock-up Excel file with your sample data, which also shows your desired result.
Thanks,
Mynda
