• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Conditional Formatting Project Management Timeline Roadmap|Dashboards & Charts|Excel Forum|My Online Training Hub

You are here: Home / Conditional Formatting Project Management Timeline Roadmap|Dashboards & Charts|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumDashboards & ChartsConditional Formatting Project Mana…
sp_PrintTopic sp_TopicIcon
Conditional Formatting Project Management Timeline Roadmap
Avatar
Shannon Davis

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
November 19, 2020
sp_UserOfflineSmall Offline
1
November 19, 2020 - 3:31 am
sp_Permalink sp_Print sp_EditHistory

Greetings,
Firstly I want to say thank you for posting such informative videos online. They have been extremely helpful!! I have been using the Interactive Excel Project Management Dashboard – Free Download YouTube video to reference quite a bit.
I am currently designing a long range roadmap calendar and am trying to create a conditional formatted formula that would pull data from the Data Sheet that if all conditions were true it would color the applicable boxes with a designated color and Title in the spreadsheet Sheet1. I added my weekends and holidays (but couldn’t get it to post like yours with them overlapping). In my case I want to keep the weekend holiday format and also have the TRUE conditions also reflected if they fall in a weekend or holiday.
In an effort to make the conditional formatting to work, I was breaking each conditional format down to individual rows. For example, Sheet1, B4:CP4 was the selected area to apply the first conditional formatting rule. Ultimately what I was hoping to achieve was IF the swimlane reads Commander AND the Legend reads 12, THEN the applicable dates START to END would be formatted with a specific color and the TITLE would be reflected within the colored blocks. I’ve tried multiple variations of the below formula without any luck.

=IF(AND(DATA!A$2=Commander,Data!H$2=12)+Data!E$2>=’Sheet1’B$3,WORKDAY.INTL(B$3,Data!F$2,1)-1>=Data!E$2)

If this works, I would then add conditional formatting rules to each row, changing the information to make TRUE statements and formatting colors based upon the Legend. Ultimately, when adding more information in the data sheet, and if it met the basic criteria to make a statement TRUE, it would then highlight the applicable dates in that row. So each row would have multiple TRUE conditions and in turn make the gantt style timeline roadmap. I hope this makes sense. Any help is appreciated. Thank you!
Attached is my working Calendar with the example in sheet1 manually highlighted as an example to show what my end state would look like. Also, I am using Microsoft Excel 2016.

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
November 19, 2020 - 8:25 am
sp_Permalink sp_Print

Hi Shannon,

Welcome to our forum! 

The first rule of conditional formatting is that you can't layer them. i.e. only one conditional format will be applied to a cell, and the first one to evaluate as TRUE wins, which is why the order of the rules is often important.

This means that if you want to highlight weekend dates with a different format to weekday dates you need two rules, one for each format. If you want different colours for the swimlanes, then you'll need two rules for each swimlane; one for weekdays and another for weekends.

Looking at your formula rule:

=IF(AND(DATA!A$2=Commander,Data!H$2=12)+Data!E$2>=’Sheet1’B$3,WORKDAY.INTL(B$3,Data!F$2,1)-1>=Data!E$2)

I don't understand what you're trying to do here, so perhaps you can write in English what it is you want to test for, and I can help you from there.

Mynda

Avatar
Shannon Davis

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
November 19, 2020
sp_UserOfflineSmall Offline
3
November 20, 2020 - 12:21 am
sp_Permalink sp_Print

Greetings Mynda,

Thank you for responding to my post.

Attached is an updated spreadsheet where I manually colored in the J5 Long Range Calendar to show what I am visually trying to accomplish from my data.

I am trying to create a formula/(s) that would take the information from the Data sheet and populate J5 Long Range Calendar with the designated colors that correspond with the numbers in the legend and then label from the Title column on the specific (Start, End) dates.

Am I supposed to create a pivot table in tab format and then adjust fields from there in order to make this work properly? Then as I add more fields into the Data sheet and Refresh, it will automatically update the J5 Long Range Calendar based upon the formula criteria and conditional formatting in each row?

I am not even sure what I am trying to accomplish is possible. The closest things I have seen to this are the project management charts and gantt charts, but those still have the info on the same sheets they are pulling the data from. Thanks again for the help.

Respectfully,

Shannon Davis

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
November 22, 2020 - 1:47 pm
sp_Permalink sp_Print

Hi Shannon,

In the attached file on the Pivot sheet you'll see I've used a PivotTable to summarise the data, then added conditional formatting for the first two colours and the weekend days to columns H through BE.

I have used Dynamic Array formulas, which require Office 365/Microsoft 365, so if you see formula errors it will be because you don't have a version of Excel that works with these functions. Let me know if that's the case.

I hope that points you in the right direction and you can create the formats for the other colours based on this example. More on conditional formatting with formulas here.

Mynda

Avatar
Shannon Davis

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
November 19, 2020
sp_UserOfflineSmall Offline
5
November 23, 2020 - 11:59 pm
sp_Permalink sp_Print

Hi Mynda,

Thank you for this additional help. I really appreciate it. I'm going to play around with this and see if it meets the criteria for my boss. Fingers crossed!! This has been a nightmare trying to find something that is easy to use and shows what he wants. Thank you again!!

Shannon

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Velouria, Nada Perovic, Denise Lloyd
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Raj Mattoo
Mark Luke
terimeri dooriyan
Jack Aston
AndyC
Denise Lloyd
michael serna
mashal sana
Tiffany Kang
Leah Gillmore
Forum Stats:
Groups: 3
Forums: 24
Topics: 6219
Posts: 27276

 

Member Stats:
Guest Posters: 49
Members: 31903
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.