Forum Discussion
Help with data and visuals
I hope i can explain this right, i have data that shows 3 things: person, start date and end date:
now i need excel to take this information and represent it as the following in the month tracker:
It would look for the charts and figure out the timeline and add them to a row with the timeframe: example is Booy 15-0ct-25 to 23-oct-25 the char would go to the next one and add his information, I hope someone can help me. I just need it to populate the month tracker, Thanks
3 Replies
With Power Query + Conditional Formatting
With data in the green table > Right-click in the timeline > Refresh
- Hope you don't have too many names as 2 Conditional Formatting rules are required per name/row in the timeline
- If you see twice (or +) the same name within the same week, this means 2 (or +) tasks/assignments are planed that week. Ex. with Abe in the picture
Sample available https://1drv.ms/x/c/1cd824d35610aacb/EUDMSudf-OVMlXyk2cjRbe0Ba4uY6DZ0PmzEg9xhSicP8Q (to download)
Alternatively, you could re-shape the data a little bit and create a stacked bar chart from it. See attached.
Give try to the following formula-
=LET(nm,A2:A7,sd,B2:B7,ed,C2:C7,dm,G1:AK1,MAKEARRAY(ROWS(nm),COLUMNS(dm),LAMBDA(r,c, IF((INDEX(dm,1,c)>=INDEX(sd,r))*(INDEX(dm,1,c)<=INDEX(ed,r)),INDEX(nm,r),""))))
