How to Incorporate Durations in COUNTIF formulas with Calendar App

Justin Ramos

Availability.PNG

Calendar.PNG

Hey all, I am having issues rolling up calendar availability with the durations. If you look at the calendar attachment picture, I want to have 3 jobs scheduled for 7/17/21 but we have a job with a duration of 2 days the day prior which makes it more like 4 jobs scheduled. I have a cross-sheet reference off the main install calendar sheet which is the availability attachment picture. If you look at the "Scheduled Level 1" column, I have a formula in there that refers back to the main Install Calendar sheet. The formula in the cell is =COUNTIFS({Installer level}, "1", {Install Date}, =Date@row) + COUNTIFS({2nd Installer level}, "1", {Install Date}, =Date@row). Right now that gives me back all jobs on that date with the the according Installer level and if we were to send two guys it includes that as well. How can I add in the duration from the day prior into the total number so I can prevent our team from overbooking our calendar?

Justin Ramos

@Krissia I actually was able to build a work around to get done what I needed it to do. I had to add some helper columns to rows with durations longer than 1 day using, =IF([Job Duration]@row = "2", [Install Date]@row + 1, IF([Job Duration]@row = "3", [Install Date]@row + 1)) and then when I roll-up the calendar availability, the COUNTIF formula is reading the helper columns as well. I just had to create multiple COUNTIF formulas but I seem to figured out a solution, thank you anyway!

Krissia B.

Hello @Justin Ramos-Flynn

Upon reviewing this and your screenshots, I am unable to fully replicate your scenario. Could you provide a screenshot of the source sheet where the countifs are referencing too as well as your calendar app settings - any additional information to fully help with replicating this to help with troubleshooting would be great! (Please block out any sensitive data)

Cheers,

Krissia

Justin Ramos

@Krissia I actually was able to build a work around to get done what I needed it to do. I had to add some helper columns to rows with durations longer than 1 day using, =IF([Job Duration]@row = "2", [Install Date]@row + 1, IF([Job Duration]@row = "3", [Install Date]@row + 1)) and then when I roll-up the calendar availability, the COUNTIF formula is reading the helper columns as well. I just had to create multiple COUNTIF formulas but I seem to figured out a solution, thank you anyway!

Krissia B.

@Justin Ramos-Flynn Awesome! I'm glad that worked out~