Hi Myndra,

I am trying to add a formula to meet the following criteria:

I couldn't attach the file as it was too big that's why I took screen shots so that you can see what I am trying to achieve. Thank you.

Hi Lisa,

Please see file attached. Note the order of the rules is important. Set them up starting with 0%. Check the Conditional Formatting Rules Manager in the example file if you have any problems.

Mynda

Hi Mynda,

Fantastic thank you so much for your time and help with this.

Can you design this dashboard to show you the start dates instead of the end dates such that you can select a view to show you projects starting instead of ending ones?

Also on the pivot table is there a way to sort with start dates?

Thanks,

Lisa

Hi Lisa,

Glad I could help.

Yes, you can base the dashboard off the start dates instead of the end dates, you'll just need to edit the formulas accordingly. And yes, you can sort the PivotTable on the start dates. More on sorting in PivotTables here.

Mynda

Hi Mynda,

Thank you, I have managed to sort my pivot table according. Sorry to keep coming back to you asking. How do I go about changing formulas to reflect the start of the project instead of the end?

Below is the 

=AND($F6=0,AND($F6=0,AND($C6<>"""",MEDIAN($C6,$D6,G$5)=G$5)),TODAY()<$C6)

Above is the formula I was using for conditional formation.

I am completely lost on how to go about it. Can you please shed some light, please let me know if you need more information. My file is too big to upload so I have attached a picture instead.

Thanks,

Lisa

Hi Lisa,

What do you mean by 'reflect the start of the project' exactly? The conditional formatting in my file reflects the start to the finish by shading the cells for the weeks the project relates to. If you don't want that, then please provide an example Excel file showing exactly what you'd expect to see. 

Note: you don't need to nest AND functions. You could simplify your formula to this:

=AND($F6=0,$C6<>"",MEDIAN($C6,$D6,G$5)=G$5),TODAY()<$C6)

Mynda

Hi Mynda,

Thank you for providing a simpler formula. What I mean is, I would like to show all projects due to start by selecting using month slicer.

At the moment I have a slicer which shows me project due to end - the slicer is based on the end dates. I am happy with that. 

I would like to replicate something similar to show projects starting dates - so have a slicer that enables me to select by month and it returns projects starting that month.

Please see desired screenshot.

Hi Lisa,

You can just add another Slicer for the start month field. That will filter the list for projects starting in the selected month/s.

Mynda

HI Mynda,

Fantastic the above has worked 🙂

Sorry to keep coming back. I hope this is the last time I come asking for help. I am nearly there now - I have used the formula you suggested above but the conditional formula seems to work for some but not all. Please see attached screenshot.

=AND($F6=0,$C6"",MEDIAN($C6,$D6,G$5)=G$5),TODAY()<$C6) - this is the formula I have used to highlight the timeline with 0% progress in blue.

The screenshot shows formulas inserted manually for demonstration purposes only.

Thanks

Lisa

Hi Lisa,

The last argument in AND is TODAY()< C6 which is the start date which is effectively 11/11/2020 < 1/11/2020 which is FALSE, hence the result. I'm not sure why you have this criteria in the formula. Presumably you can just remove TODAY()< C6.

Mynda