Workbook Upgrade to have a starting date & end date for events

Hello Wizards,

I have a calendar which works well (c-f with 3 events/day) but if an event should last 2 weeks for example, I would need to add 10 rows in my data table (one for each day). I would like to upgrade it to have a starting date & end date, avoiding manual work. Attaching the workbook for your reference
Also, is there any approach / formulas that could make the same events from the same data table to populate the 3rd sheet, this time based on "Process" column, but at a monthly level? Keeping only the description & period would be enough

Thank you!

Welcome to the forum.

Thread titles here need to be descriptive of the issue and not generic, soI have changed yours today. Please remember this requirement in future. Thanks.

I will have a look at your workbook.

For example, in H17, change this:

=IFERROR(INDEX(Data!$B$1:$B$50,SMALL(IF(Data!$I$1:$I$50='Monthly View'!H16,ROW(Data!$I$1:$I$50)-ROW(INDEX(Data!$I$1:$I$50,1,1))+1),1)),"")

to this:

=IFERROR(INDEX(Data!$B$1:$B$50,SMALL(IF(Data!$H$1:$H$50<='Monthly View'!H16,IF(Data!$I$1:$I$50>='Monthly View'!H16,ROW(Data!$I$1:$I$50)-ROW(INDEX(Data!$I$1:$I$50,1,1))+1)),1)),"")

Is this what you mean?

By the way, there is no data in the 'third sheet'. If you want help with that, please manually mock-up what you want it to look like.

Hey Ali,

I've tried to add 2 replies but it just won't work, and I don't have any clues why

The formula you added is superb for the colored parts, but I need the description for every event next to them, also.
Can something similar be done with =IFERROR(INDEX(Data!$C$1:$C$50,SMALL(IF(Data!$I$1:$I$50='Monthly View'!B4,ROW(Data!$I$1:$I$50)-ROW(INDEX(Data!$I$1:$I$50,1,1))+1),1)),"") ?

Also, for the 3rd sheet, there's nothing because I am not sure how to add the Process in Data column there. For example, if Process A takes place between 15Jan-20Jan, it would appear on the yw sheet under Jan having the description and the period if it makes more sense now :D

Thank you!!

I'll reply to the thread as well but I am not sure what's happening

The formula you added is superb for the colored parts, but I need the description for every event next to them, also.

The description formula needs updating following the same pattern as the example I have given you to look at TWO dates.

e.g. =IFERROR(INDEX(Data!$C$1:$C$50,SMALL(IF(Data!$H$1:$H$50<='Monthly View'!J16,IF(Data!$I$1:$I$50>='Monthly View'!J16,ROW(Data!$I$1:$I$50)-ROW(INDEX(Data!$I$1:$I$50,1,1))+1)),1)),"")

Also, for the 3rd sheet, there's nothing because I am not sure how to add the Process in Data column there.

I know, but that's not what I am asking for. Please mock up MANUALLY what you want to see there based on the sample data, then we can suggest how to do it.

Thanks. That's clear now. I have to go and do something, but if nobody has responded when I get back, I'll have a go at it for you. It should be simply a matter of tweaking the formula you already have for the other sheet to look at the whole month and fllter on the correct data.

As you have 365, this may be easier than it would otherwsie have been.

Thanks Ali, I've tried to but without any luck. I will be waiting your/someone to reply

OK - I'm a bit tied up for now, but promise I'll look later if nobody else steps in (but I am pretty sure someone will).

Thanks for your message. This post will bump the thread.

I ran out of time yesterday, but looking again at your data, given its nature, I am sorry, but I am not going to be able to provide you with a solution.

Be aware that there have been well in excess of 200 views of this thread and I put out a call for help yesterday: the fact that nobody has yet jumped in is not a good sign, I am afraid.

EDIT: Ignore this! I was thinking of another thread ... Sorry.

I will have a look now.

In B1:

01/01/2022

In C1 copied across:

=EDATE(B1,COLUMN()-2)

Set formatting for these header cells to custom: mmm

For example, in B8:

=IFERROR(INDEX(Data!$C$1:$C$50,SMALL(IF(Data!$H$1:$H$50 > =B$1,IF(Data!$I$1:$I$50 < EDATE(B$1,1),IF(Data!$G$1:$G$50=$A$8,ROW(Data!$I$1:$I$50)-ROW(INDEX(Data!$I$1:$I$50,1,1))+1))),1)),"")

This will pull the event.

You'd need to concatenate the other data (see the yellow cell in the workbook).

AliGW on MS365 Insider (Windows) 64 bit

	A	B
8	"C"	JIM/ Do Something 1 + "A" + 01/01/2022 to 05/01/2022

Sheet: Yearly View

You're welcome and thankd for the rep.