Separate data and text from one cell to provide a duration with a summary

[danielphildp]

Hello, There.
I am trying to solve a problem with our current data collection at my office. As of now every Friday the entire team use a shared excel sheet to update their assigned project about the next activities with a date and a small description. For example " 11/17/23 Awaiting proposal approval; 11/10/23 project cost review; 11/03/23 contract proposal due on Monday...........etc." So the project history is in one cell showing as I mentioned.

I am trying to get a monthly data table showing a monthly duration (start and finish) with all notes associate with it for that month (and so on for each month). I have attached a snip how our data looks like and what I would like to accomplish, I am new to excel world and I am not as smart as anyone in this group. I would really appreciate if you could help, guide, or explain to me how we can accomplish this.

I have tried to Delimiters by semicolon, but I can't group weekly dates to months and separate texts to group.

 

[BrerRabbit]

That's alot of information in one cell to try and break down. Is it possible to break this down into two sheets: the first is the data entry whilst the second is the "report" which will show the data however you want it. There is a way to break down the cell, but there's too much here to formulise what goes into which cell, hence the data entry side.

 

[BrerRabbit]

My other question is: for each "Activity Step" what is the maximum number of steps in each that there would be? For example for teh green you've got several, as well as teh black, 4 for the blue and 5 for the red. Would it be 10? or 20? and which excel version are you using?

 

[vladimiratanasiu]

What method do you apply, in order to group the selected data? Is it always the colour, or did you use it only in this case manually?

 

[danielphildp]

That's alot of information in one cell to try and break down. Is it possible to break this down into two sheets: the first is the data entry whilst the second is the "report" which will show the data however you want it. There is a way to break down the cell, but there's too much here to formulise what goes into which cell, hence the data entry side.

I am very flexible with ideas to generate a better report. The only problem I am facing is that “the way of doing business” updating projects status will remain the same “by adding information to that one cell”. I am trying to pull out a report.
If the report I am trying to accomplish is impossible but you see a better way please let me know.

 

[danielphildp]

My other question is: for each "Activity Step" what is the maximum number of steps in each that there would be? For example for teh green you've got several, as well as teh black, 4 for the blue and 5 for the red. Would it be 10? or 20? and which excel version are you using?

each color “activity step” is related to how many Fridays in a month. Some months would have 4 Fridays which is = 4 steps, while other months could have 5 Fridays. The excel version is 2019 or 2021.
(The color coded text is for explanation purposes only, none of the text needs to colored)

 

[danielphildp]

What method do you apply, in order to group the selected data? Is it always the colour, or did you use it only in this case manually?

I did everything manually showing the intent of what I would like to accomplish. (Color texted is for demonstration purposes only, none of the text needs to be colored). The goal is to have a duration collection weekly dates into monthly duration (start to finish) and collecting all the notes related to the month next to it.

 

[BrerRabbit]

I am very flexible with ideas to generate a better report. The only problem I am facing is that “the way of doing business” updating projects status will remain the same “by adding information to that one cell”. I am trying to pull out a report.
If the report I am trying to accomplish is impossible but you see a better way please let me know.

The report is doable. There's just a fair amount of excel formula that you are going to need to learn. This is a bigger job than you realise. BUT a really good way to start learning excel.

The first thing you need to understand is that Excel doesn't do calendars very well. The second thing you have to understand is that if you are going to use this spreadsheet for more than one year than you need to standardise the calendar so that it can be used each year. You'll also need to learn tables in excel, which are easy and will be of great assistance to you.

This is something I thought up just off the top of my head. Notice that the dates for each Friday are ABOVE the table. This is deliberate for later. That way when you expand to include several years the weeks will be the same but you can still see the dates, as obviously the date will change from year to year. The formula to calculate each Friday date is based on H23 where the year is.

You're going to have to learn to include more than one piece of data for each cell in the "calendar array", as I like to call it. Otherwise your spreadsheet will explode and become unweildy. You'll need to learn your left, right, mid functions to use this code to your advantage.

Take a look and see what you think. This is something you can really start building on. Remember, the more you catch in your data entry the more informative your reports will be.