Calendar creation using existing data

[ssheppard06]

Hi, I am looking to use existing data, with dates and times of classes/sessions and create a calendar weekly view of these sessions. Can anyone help with this issue?

[CK76]

Not without sample file Please upload sample file using "Go Advanced" button found at edit menu. Then find "Manage Attachment".

[ssheppard06]

Hi,

Thanks. Please see attachment The data that need to be pulled into the calendar is labelled "data". And i would ideally like it in the tab labelled "Weekly". When pulling it into the tab labelled "calendar" the vlookup formula only gives me the first session on that date. I would like to include all sessions that are on that date. All help is greatly appreciated.

[CK76]

Looking at your Data table. There are way too many rows with issues.

1.[Date] has mix of date values and text values (Ex: Oct 23 - 31)
2. You have wrong end time values for quite a few (mostly 12:30 AM instead of 12:30 PM, but there may be others).

Can you fix these issues and re-upload?

[ssheppard06]

Thanks for catching those data errors. i have fixed it and reattached.

[CK76]

Something like attached?

[ssheppard06]

Wow this is great. yes this works! only thing, is there a way to automatically or easily merged the cells of the sesisons to fill the block of time? For example if 9/4/17 is Labor day it shows labor day down the line for each interval. How can i easily merge the cells so Labor day holiday is from 9-4pm shown once (a macro possibly). Thanks so much for your help with this. This is amazing!

[CK76]

In general, merged cell = EVIL.

It's only function is visual eye pleaser, and it wreaks havoc on most functions, codes and other worksheet operation.
I advise everyone to avoid it like a plague. I've warned my co-workers that I'll not help them on any workbook that has merged cell.

If you must visually group block, I'd suggest using Conditional Formatting or use different structure to summarize data.

[ssheppard06]

Lol. Great to know. Yes it is only for visual pleaser as this is for another Office that is not too Excel Savvy, and would like it to look like a calendar. Thanks again!

[CK76]

If you want to go with CF method, you will need hidden column with formula. See link for tutorial.
https://www.extendoffice.com/documen...-on-group.html

[ssheppard06]

I tried to use the format in the link but it doesn't seem to give me what i'm looking for. I'm attached the updated spreadsheet. The formula is in column I. Am i doing something wrong? I need it to highlight the repeated sessions on a given day.

[CK76]

Ah right, you have blank rows in the mix. That complicates things a bit.

Here's sample that I did, not different color for each distinct value, but based on contiguous time range & group.

I.E. If there is empty cell between, same color is kept. If contiguous range and has 2 different session back-to-back, color changes.

[ssheppard06]

Yes...this is better! Very helpful