Populate start and end times on calendar view

[Seba Robles]

Hello all,

I'm having trouble trying to come up with a formula that allows me to do the following...

I have a EXCEL WORKBOOK with a table that lists activities by date and time assigned to a group.

On the second sheet I have a "calendar view" that is supposed to visually show how the activities by group stack on each date.

The screenshot below is the desired outcome. I can't find a formula that matches the date on row 3 with the times on column A.
Aside from that, after the match is found, I would like the cells to populate with the respective duration of the activity.

For example, if we look at activity A from group A1 on the table above, it is scheduled for 6/26, starts at 8am and ends at 8:30am. On the calendar below, cells B4, B5 get populated with that information from the table.

P.S. I used conditional formatting to paint the cells in blue for A1, purple for A2, and red for B1.

Would appreciate any pointers. Let me know if more info is needed.

Thanks in advance!!

 

[Fluff]

Duplicate to: Index Match Match using dates and times

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.