Hello everyone
I'm currently trying to set up a calendar for projects. I inserted an excel sheet with an approximate way it could look like. The goal would be to insert start and end dates and excel automatically colours the corresponding months. This design is not fixed, if you have other suggestions I'd be glad to take them!
Last edited by faoiltiama; 12-24-2020 at 02:58 PM.
Is this just for a visual display purpose or will you be needing other functionality?
My first thought would be to use a pivot table and chart
Mainly visual, but dates should be changeable. Sadly, I have no experience in pivot tables, but I will browse through this forum a bit
Perhaps this will help:
1. Unmerge cells in row 1, use horizontal alignment across selection (Ctrl + 1) instead. E1 = YEAR(E2), copy to Q1 and AC1.
2. For row 2: E2 = MIN(A5:A11), F2:AN2 =EDATE(E2,1). Custom format "m"
3. Insert column for Start Date
4. Conditional formatting formula for E4:AN7 is: =AND(ISNUMBER($A4),$A4<=E$2,$B4>=E$2)
5. Conditional formatting formula for E8:AN11 is: =AND(ISNUMBER($A8),$A8<=E$2,$B8>=E$2)
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Wow, this is exactly what I was looking for! *.* Thank you for this Christmas gift! One more question, could I change it somehow, that if something starts on the 2nd of January, that January is still coloured?
Yes.
Change the formula in cell E2 so that it reads: =EOMONTH(MIN(A5:A11),-1)+1
Change the conditional formatting formula for E4:AN7 so that it reads: =AND(ISNUMBER($A4),$A4<=EOMONTH(E$2,0),$B4>=E$2)
Change the conditional formatting formula for E8:AN11 so that it reads: =AND(ISNUMBER($A8),$A8<=EOMONTH(E$2,0),$B8>=E$2)
Let us know if you have any questions.
Hi there, it's me again, hoping you'll see this question
You really helped my project work, thank you! Now I'd like to add something:
At the end of some finished tasks, I'd like to put a text clarification. Which text is stated in column D (I'll hide this part later)
As this is not conditional formatting I don't know how to tell Excel to do so. ¨
Attached I have the excel how I'd like it to look but without the needed function /macro/condition or whatever else is needed to solve this.
Little extra question: can I also add Symbols at the end/beginning of the coloured bars? f.ex a rhombus?
Best wishes
To add a rhombus (diamond) symbol paste the following into cell E5 then drag the fill handle over and then down to cell AN11:Formula:
Please Login or Register to view this content.
While those cells are still selected change the font to wingdings.
I feel that adding the clarifications from column D is going to require VBA (if it can be done) of which I have very little knowledge.
Let us know if you have any questions.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks