Extremely Dynamic Auto Updating Calendar

Hello, I am trying to create an auto updating calendar in excel.

The data I have is as follows:

Several different restaurant all with different start times and end times for each meal breakfast, lunch, dinner
I also have information whether or not the restaurant is booked at either of those specific times

I want to write a code that can read all of this data and create a highlighted box with the word "reserved" inside of the meal period on each specific day for each month.

I was thinking of using IF statements but it seems way too long to do this, as there are several restaurants and all have different breakfast, lunch, and dinner start / end times.

Is something like this even possible in excel?

Thanks!

Yuo can do almost anything in Excel, but how it can be done is very dependent on your workbook layout and your expected output.

How is your data arranged? Where do you want to place "Reserved"? In a table? By restaurant? By day? By.....?

My data is arranged into columns with the restaurants name, time it was reserved, time the reservation was lifted, date, fiscal week

I also have another sheet on the excel file that tells me all the meal period hours for each individual restaurant.

I want to have calendars for each month that are auto updated when you put the restaurants name at the top. I have shown an example of the format im looking for below

March
Restaurant Name
FW Sun Mon Tues Wed Thurs Fri Sat
22 25 26 27 28 1 2 3
22 B
22 L
22 D
23 4 5 6 7 8 9 10
23 B
23 L
23 D
24 11 12 13 14 15 16 17
24 B
24 L
24 D
25 18 19 20 21 22 23 24
25 B reserved
25 L reserved
25 D reserved
26 25 26 27 28 29 30 31
26 B
26 L
26 D

The formatting got lost when I posted it but it should look just like a normal calendar
Month at top
Restaurant Name
Dates below the days of the week. and days broken down into breakfast, lunch, and dinner. I have the calendar format, I just need to insert the data

Try this - get your data into the columns that I used, or move the columns in my workbook to agree with your data placement prior to copying the data into the database.

Wow thanks! That really helps. One thing about my data though is that my raw data is in a time format such as 8:00am, 11:00pm, etc. I'm trying to create an equation that can find all the times between every restaurants specific breakfast time, lunch time, and dinner time and put it into the category of breakfast, lunch, and dinner. This has been very challenging.

So I have data from each restaurant on 5 minute intervals of reservations being booked, but it doesn't specify whether or not this is during breakfast, lunch, or dinner. It only tells me the exact time the reservation was either lifted or booked.

Thanks again for your help, I appreciate it immensely

Again, it depends on how you have set up your data table - Look at the new sheet that I added to this version.

Thank you again, I really appreciate it.

I am having trouble so I uploaded a snippet of my data.

The complication is that there are multiple reservations per day because there are different types of tables, but I am just trying to show if there are any open reservations at all for a specific meal period (Being that there are more reserved than unreserved for a specific meal period is criteria for it to say reserved on the calendar). Just having the word "reserved" under the specific meal time is sufficient. I included the type of table that was reserved but I feel like including that would be too hard to do.

Im also trying to put the reason for the reservation in a notes section somewhere outside of the calendar. If that's even possible.

Thanks again for all your help, this really means a lot to me.

I'm not really sure what you want from that set of data - for any time frame, you could calculate the percent of tables or seats reserved if you knew the number of tables or seats. But then you also run into the issue of wanting it by day, where you could have the same table reserved multiple times for each period, if the reservations are short enough of the period long enough....

Im trying to build an excel query that can read the reservation start time and end times (comparing them to each restaurants specific meal times), add up if there are more reserved than unreserved, meaning that there are still tables reserved for that specific day/meal period/restaurant.

Say we didn't need to worry about the types of tables or seats, we just want to know if there are any reservations of any type still open for the given meal periods.

The excel calendar you made looks exactly like how I'm trying to get mine to look, I just need to figure out a way to get my data to fit.

I created a code to assign each reservation with a specific meal period (based on each restaurants individual meal times), B = breakfast, L = lunch, D = Dinner, A = All day. But I am having trouble getting this to assign to Breakfast (Note this is the code for my original data file, not the snippet I posted here)

=IF(SUMIFS(Lookups!$U:$U,Lookups!$Q:$Q,'Raw Data'!$AF12,Lookups!$R:$R,"<="&'Raw Data'!$AJ12,Lookups!$S:$S,">="&'Raw Data'!$AK12)=1,"B",IF(SUMIFS(Lookups!$U:$U,Lookups!$Q:$Q,'Raw Data'!$AF12,Lookups!$R:$R,"<="&'Raw Data'!$AJ12,Lookups!$S:$S,">="&'Raw Data'!$AK12)=2,"L",IF(SUMIFS(Lookups!$U:$U,Lookups!$Q:$Q,'Raw Data'!$AF12,Lookups!$R:$R,"<="&'Raw Data'!$AJ12,Lookups!$S:$S,">="&'Raw Data'!$AK12)=3,"D","A")))
This is my logic to insert "reserved into the different meal periods of a calendar"

I then used this code to assign each calendar days meal period with a "reserved" text, if any reservations are still active on that day / meal period. (Note this is the code for my original data file, not the snippet I posted here)

=IF(COUNTIFS('Raw Data'!$AN:$AN,"Reserved",'Raw Data'!$AO:$AO,$K17,'Raw Data'!$AP:$AP,O17,'Raw Data'!$AF:$AF,$F$3,'Raw Data'!$AS:$AS,$L18)>COUNTIFS('Raw Data'!$AN:$AN,"Unreserve",'Raw Data'!$AO:$AO,$K17,'Raw Data'!$AP:$AP,O17,'Raw Data'!$AF:$AF,$F$3,'Raw Data'!$AS:$AS,$L18),"reserved",IF(COUNTIFS('Raw Data'!$AN:$AN,"unreserved",'Raw Data'!$AO:$AO,$K17,'Raw Data'!$AP:$AP,O17,'Raw Data'!$AF:$AF,$F$3,'Raw Data'!$AS:$AS,"A")>COUNTIFS('Raw Data'!$AN:$AN,"unreserved",'Raw Data'!$AO:$AO,$K17,'Raw Data'!$AP:$AP,O17,'Raw Data'!$AF:$AF,$F$3,'Raw Data'!$AS:$AS,"A"),"reserved",""))

Does this seem possible? Im starting to lose hope haha

Can you post your workbook with the Lookups sheet? Why would SUMIFS only be 1? Why are you looking at <= and >= for two different columns and two different times?

I have uploaded my spreadsheet with the lookups data as well as my rudimentary version of the calendar. I omitted some lines of data because the file is so massive.

As an attachment to your message?

Its on the third post

It's in post #3, not the original post.

First problem - your start and end times for reservations are entered as date/time values formatted to only show the time, where you need to only use time with this:

SUMIFS(Lookups!$U:$U,Lookups!$Q:$Q,Database!$AF2,Lookups!$R:$R,"<="&Database!$AJ2,Lookups!$S:$S,">="&Database!$AK2)

You can enter only times, or change your formula to only look at the time value for the entries in AJ and AK:

SUMIFS(Lookups!$U:$U,Lookups!$Q:$Q,Database!$AF2,Lookups!$R:$R,"<="&MOD(Database!$AJ2,1),Lookups!$S:$S,">="&MOD(Database!$AK2,1))

The second thing is that almost all of the reservations extend beyond the time periods of a single defined meal. Is that typical?

Thanks! Ill check that out.

Yea its normal. That's the time the reservation booked and the end time is when it was unbooked. So I'm trying to get a calendar that can find out which ones are still booked and have it highlighted for its specific meal time. Even if its all day.

So do you want Reserved in all three slots if there is an all day event, or only if there are more total events than you have slots?

If there are more reserves than unreserves for a given day/time/restraint/month/year/FW, then I want it to say reserved under the specific meal time its reserved. There may be overlapping reserves and that okay.

The start time column of data is when it was reserved, and end time is when it was lifted. If there are the same number of reserves as unreserves for a specific day/time/restaurant then it shouldn't say anything. If there are more reserves than unreservers for a specific day/time/restaurant/month/year/FW then it should say reserved under its specific meal time.

If that happens to be all day. then all meal times will say reserved.

Thanks a lot, let me know if I need to be more clear

Which post do you want deleting?

We don't do that unless there is a very good reason. What is the reason?

Please stop deleting the contents of your posts. This is a public forum. The contents will be replaced - do NOT attempt to delete them again.

Send me a private message explaining what is sensitive and I shall remove just that data.

I have locked this thread so that you cannot remove your posts - removing everything leaves a thread that is useless. This is a public forum and everything should be shared.

EDIT: I have looked at the attachments and can find nothing sensitive (that is specific to any individual business) in the thread. There is not even any mention of the business name. As the rest of the thread may be useful to others, it will remain.