Best practices/overbooking for Smartsheet calendar app?

Justin Ramos

My company (Bath remodeling company) has our install calendar currently on excel and we do about 22 jobs a day. We are planning to migrate our calendar to Smartsheet calendar app and we are having issues finding a way to provide block outs for some Installers when they request off or how to prevent overbooking. I created a report to show availability based off the number of entries we have but when we go negative there isn't anything preventing someone from filling out a form fill and adding to an already overbooked day. The best I could do would be to show the form and the report on the same dashboard and when inputting in a new job, you would have to confirm that the day isn't negative (because of overbooking) before inputting another entry on the calendar. If there are any best practices when it comes to Smartsheet calendar app in general please let me know since this is a new feature for us!

Paul Newcome

Try this for the COUNTIFS:

=COUNTIFS([Main Installer Level]:[Main Installer Level], "1", [Install Date]:[Install Date], [Install Date]@row, Created:Created, @cell <= Created@row)

Now that we have the count, we can wrap that in a COUNTIFS to say that if that number exceeds the maximum, flag the row.

=IF(COUNTIFS([Main Installer Level]:[Main Installer Level], "1", [Install Date]:[Install Date], [Install Date]@row, Created:Created, @cell <= Created@row) > 20, 1)

Paul Newcome

Having the report and form on the same dashboard is going to be the best solution I can think of in this particular case.

You could also add in some extra automations to send out an update request to whoever filled out the form alerting them that they have overbooked and need to adjust their booking.

Justin Ramos

@Paul Newcome Thank you for the feedback! How would I set up automations to send an update request to who submitted the most recent form? I have the schedule availability on another sheet that has cross references to the main install calendar sheet based off the entries submitted so would I need to move all of that info onto the one main sheet and hide it in order to have the ability to notify someone that we are overbooked?

Paul Newcome

Which sheet captures the form entries?

Justin Ramos

The main sheet is the one with the form since the form fills are essentially the install/customer info. I then created a separate sheet with cross references with a countif formula to count the number of jobs we have on a given date. It gets complicated because we have 3 different levels of installers and all 3 levels are counted to give the total number of openings and then I have another column next to those numbers to deduct as new entries are submitted to make sure we don't over book

Paul Newcome

You could insert a column on the main sheet with a formula containing cross sheet references to flag a row if it creates an entry that causes an overbooking. You would write it so that it incorporates a COUNTIFS to count how many previous entries there are for that particular date then compare that to how many bookings are available in total. Any time the COUNTIFS is greater than the total amount available for that date, flag that row. Then you can set your update request to trigger based on that flag.

Justin Ramos

Smartsheet pic.PNG

@Paul Newcome I have been trying to make this work and I am still having issues. I've tried =COUNTIFS([Main Installer Level]:[Main Installer Level], "1", [Install Date]:[Install Date], [Install Date]@row) and that works but gives me the same value for all rows so if someone submitted a row that was overbooked, it would notify everyone who submitted for that date because all the numbers in the hidden column would change. I tried an index match formula to the availability sheet and it's the same issue. I wonder if I used the Distinct formula I could add one row at a time until it hits the max number to be overbooked. I tried =COUNT(DISTINCT(COLLECT([Main Installer Level]:[Main Installer Level], [Main Installer Level]@row, [Install Date]:[Install Date], [Install Date]@row))) but that only give me a 1 back. Basically, I need it return a distinct value back from counting the Install level needed based off the install date with other entries in the same sheet so then if say 20 entries are submitted with the same install date and the same required installer level it should return 1, 2, 3... until it hit 20 and then I could built the automation to update rows based off when it hits the cap and is marked as overbooked. What do you think?

Paul Newcome

Try this for the COUNTIFS:

=COUNTIFS([Main Installer Level]:[Main Installer Level], "1", [Install Date]:[Install Date], [Install Date]@row, Created:Created, @cell <= Created@row)

Now that we have the count, we can wrap that in a COUNTIFS to say that if that number exceeds the maximum, flag the row.

=IF(COUNTIFS([Main Installer Level]:[Main Installer Level], "1", [Install Date]:[Install Date], [Install Date]@row, Created:Created, @cell <= Created@row) > 20, 1)

Justin Ramos

@Paul Newcome I used your formula and it seems to work! Thank you for your help!

Paul Newcome

Happy to help. 👍️

Justin Ramos

Hey @Paul Newcome ,

So far your formula has been working great however, I am experiencing a weird issue. I have a job marked for a level 3 Installer but the level 1 check box is being checked. For background, the job below it is an overbooked level 1 job but it's weird that it would check it even though "Main Installer Level" column is a 3. Below is my formula I am using, I needed to add a + COUNTIFS to the original because I have a column for if there is a 2nd installer assigned to a job. Any advice? Thanks!

=IF(COUNTIFS([Main Installer Level]:[Main Installer Level], "1", [Install Date]:[Install Date], [Install Date]@row, Created:Created, @cell <= Created@row) + COUNTIFS([2nd Installer Level]:[2nd Installer Level], "1", [Install Date]:[Install Date], [Install Date]@row, Created:Created, @cell <= Created@row) > [Level 1 Availability]#, 1)

Paul Newcome

It is because your formula is specifying Level 1. Try using a cell reference instead.

=IF(COUNTIFS([Main Installer Level]:[Main Installer Level], [Main Installer Level]@row, [Install Date]:[Install Date], [Install Date]@row, Created:Created, @cell <= Created@row) + COUNTIFS([2nd Installer Level]:[2nd Installer Level], [2nd Installer Level]@row, [Install Date]:[Install Date], [Install Date]@row, Created:Created, @cell <= Created@row) > [Level 1 Availability]#, 1)

You are also going to have to adjust the [Level 1 Availability]# portion to look at the level 3 availability.

Justin Ramos

@Paul Newcome the  [Level 1 Availability]# comes from the sheet summary which is linked from a master sheet that we assign how many we can schedule per day. Saturday's are the only days that are different so I made it separate. How do you recommend I adjust it to look at the other levels? The other check boxes are the same formula but reference "2" or "3" for the levels and the availability that matches it.

Below is the "master availability" sheet we use to make sure we aren't over booked. These are manually entered by our operations department based off how many guys we have available by level. The Blue row is what is being linked, I couldn't figure out a way to match the date to what is entered for the original formula. (right now it's just one set number even if you look at 9/25, we only have 9 available level 1s)

Paul Newcome

You would change the bold portion below, but it looks like you have separate columns for each level and that you have already taken care of that.

=IF(COUNTIFS([Main Installer Level]:[Main Installer Level], [Main Installer Level]@row, [Install Date]:[Install Date], [Install Date]@row, Created:Created, @cell <= Created@row) + COUNTIFS([2nd Installer Level]:[2nd Installer Level], [2nd Installer Level]@row, [Install Date]:[Install Date], [Install Date]@row, Created:Created, @cell <= Created@row) > [Level 1 Availability]#, 1)