How to tweak formula to change calendar to include date range

Hello,

I've canibalised another sheet already created for a different user by Pete_UK, attempting to make it work for my own project.

Can someone tell me how I might adapt the following code to be able to calculate for a date spread between two columns:

=IFERROR(INDEX('Event Log'!$B:$B,MATCH(G3&"_"&$C4,'Event Log'!$D:$D,0)),"")

If it's impossible to understand without context, attachment is attached.
See Calendar (2) tab for the canibalised version.

I've updated the attachment, fixing a small error with the dates and adding another small piece of knowledge that will probably be helpful...

What I have done in other files where a date range is needed is to expand the activities table so that there is one record for each day in the date range, and this is then used as the source data for the calendar.

I'll try and dig an example out for you a bit later on.

Hope this helps.

Pete

Okay, there is an example workbook in Post #29 of this thread:

https://www.excelforum.com/excel-for...-workbook.html

In the events_data sheet I have moved the main data entry table to the right, and used two other tables to automatically generate records for each day required. The green columns (A and B) just pick up the number of days for each date range and then generate a cumulative sum of those days. Note that A2 is empty and B2 contains zero - this is important for the other formulae. This also means that the formulae in A3 and B3 (and subsequent rows) are actually looking at row 2 of the main data table.

The blue area is the part which actually expands the data to give one record for each day, generating a date and reference number that the calendar can make use of. You can also control what is displayed on the calendar (column G), and in this particular case the OP needed a Crew column which controls the conditional formatting on the calendar.

The formulae in the calendar sheet are essentially the same as you already have, but are now looking specifically in the blue area of the Events sheet.

Hope this helps.

Pete

Thanks Pete!
I'd like to try and use Power Query for as much of this as I can (seems like it's a good way to keep things 'smart' but I might be wrong?)

I've created an auto table that lists out the dates, similar to your example. Now I'm not sure if I'm able to have Power Query create the IF/COUNT function, as you have it in the formula.

Is it possible?

See new tab in the attached for "Get Date Event List" - I'm hoping you can open the Power Query function to see the steps I created to make that list.

I've attempted to add a colum and adding the following formula, but I'm getting a 'Token Comma Expected" error, hovering over the 'v' in 'Event'

=[Event Date List]&"_"&COUNTIF(B$2:[Event Date List],[Event Date List])

Screen shot showing highlighted 'error' here:

Attachment 820362