Create Dynamic Project Timelines in Excel with Conditional Formatting

[@hoangthuaduy4864]

Awesome!! Excellent explanation.. This is exactly I was looking for. Thank you so much for the video!!

[@MintyAnalyst]

Glad it was helpful! 😊

[@rambabu77]

Awesome!! Excellent explanation.. This is exactly I was looking for. Thank you so much for the video!!

[@MintyAnalyst]

Glad it was helpful :)

[@scottdavis90]

SUPER HELPFUL! THANKS!!!!!

[@MintyAnalyst]

Glad it helped! ✨

[@sumitkchaudhari]

Excellent Man...!!!

[@MintyAnalyst]

glad it was helpful ;)

[@MajaWilhelm-f4f]

@mintyanalyst is there a way you can add to the formula to make the formatting cell colour change based on another column e.g. a status column or category column?

[@MintyAnalyst]

I don't think there should be any issue adjusting the formula to do this as well. I've always dreaded conditional formatting, as it starts to get really complex the more you customize it. Honestly, I can't even remember how I came up with this formula back when I made the video 😅

[@Dennis-pj9qt]

Thanks a ton! Really great video, easy to follow. Saved me a lot of time. Is there a way to retrieve the "task" text from column B and place it over the yellow bars? I tried =IF(AND(I9.Interior.Color = RGB(91,155,213), H9.Interior.Color RGB(91,155,213)), B9, ""), but since the cells have conditional formatting the formula does not recognize the color.

[@MintyAnalyst]

I gave it a quick try but couldn't figure it out... It's Excel, so I am 100% it's possible, but not sure how 😅

[@plamenanastasov5809]

I really like you!

[@MintyAnalyst]

thanks bro 🤣

[@bubbyshines4171]

Great vid! How can we do quarterly or monthly view?

[@MintyAnalyst]

Hi there, there is already a monthly view in the tutorial, as for a quarterly view, you just need to extend the timeline dates' formulas to include another IF for a quarterly view. Hope this helps :)

[@jackwerch7226]

Is there a way to change the color of the highlight based on a specific team performing the activity?

[@MintyAnalyst]

Hi Jack, the way I would probably approach this is to have an additional condition in the conditional formatting that checks if the team is "ABC". Then I would copy the rule as many times as the number of teams, change the condition and change the color. There may be a way to do it so that you don't have to explicitly state the teams, but I can't think of it... Hope this helps and good luck, let me know if you figure it out!

[@philmonhaile7328]

How would you account for a “Yearly” option on the timeline if I wanted one?

[@MintyAnalyst]

What you can do is change the dropdown to have an "Yearly" option (from data validation), and then switch the formula of the timeline cells (starting from G10) to be: =IF($C$7="Daily",F10+1,IF($C$7="Weekly",F10+(7-WEEKDAY(F10,2)+1),IF($C$7="Monthly",EOMONTH(F10,0)+1,IF($C$7="Yearly",DATE(YEAR(F10)+1,1,1),"Err")))) Then, whenever you switch to "Yearly", it will calculate as Jan, 1st of the next year from the previous timeline cell. As you adjust the duration of the items, e.g. change "Sign Term Sheet" to be 480 days, then you'll see the Conditional Formatting will pick it up and spill it over CY2022 and CY2023. Hope this helps 😊

[@mariettequinn1837]

Awesome! Is there a way to make this a 12 month timeline (with no year) and have it expand and contract depending on what view you are looking at? 365 days or 52 weeks or 12 months? I want to create a cyclical work timeline. Also hoping to group it by semester: fall, spring, summer with fixed dates, fall from 08/16 to 12/31, spring from 01/01 to 05/15 and summer from 05/16 to 08/15?

[@MintyAnalyst]

Hi Mariette, I can't build the file for you, but here's an answer on how to modify the timeline to include an Yearly option from below: What you can do is change the dropdown to have an "Yearly" option (from data validation), and then switch the formula of the timeline cells (starting from G10) to be: =IF($C$7="Daily",F10+1,IF($C$7="Weekly",F10+(7-WEEKDAY(F10,2)+1),IF($C$7="Monthly",EOMONTH(F10,0)+1,IF($C$7="Yearly",DATE(YEAR(F10)+1,1,1),"Err")))) Then, whenever you switch to "Yearly", it will calculate as Jan, 1st of the next year from the previous timeline cell. As you adjust the duration of the items, e.g. change "Sign Term Sheet" to be 480 days, then you'll see the Conditional Formatting will pick it up and spill it over CY2022 and CY2023. Hope this helps 😊

[@mariettequinn1837]

@@MintyAnalyst Thank you! This is so helpful.

[@davidworthington8487]

Great video is there a way to add second condition for example planned start date 4/7/22 going take 3 days so should end 6/7/22 but actual end date is 8/7/22 hoe to have that extra day as gone over a different color

[@MintyAnalyst]

Hi David, you should be able to make a second copy of the conditional format, and add an IF statement in it to check whether the date is both below today (or another column with actual end date) and after the deadline. Hope this helps :)

[@carlosdonoso1717]

is there a way to add a milestone by using a symbol or character?

[@MintyAnalyst]

Hi Carlos, not sure what you mean by adding a milestone, but I believe you can either add a character manually and then use conditional formatting to highlight it, or you can even probably adjust the formula to highlight it based on some other information. Good luck and hope this helps at least a tiny bit ✨

[@crowsandcrows]

Hi! Hope you see this. I am really struggling right now😢 Will this still works even if i changed the start or end date?

[@MintyAnalyst]

Hi Zea, yes, it should work just fine ✨