Counting Days in Calendar View or in Sheet Between Date Ranges

lmarantos

We are implementing a system where people will be submitting time off information across our company. We have multiple regional directors (30+), so I'm trying to figure out how best to show each of them how many people they can anticipate to be off each day of the year within their region based on the date ranges we're collecting (please see the visuals).

If I do this in a sheet, will I need one column per day of the year? If so, will I then need to update each of those formulas each year based on the new year number?

Is there any way to avoid having 365 columns? I was thinking maybe the calendar view, which does give a great visual but we will have so many submissions they will never be able to see each of them on each day, so is there a way to have it calculate a total using that view?

Or, is there a better way we could do this?

Thank you so much for your time and thoughts!

Paul Newcome

You could use a separate sheet that has a single date column in it. In this column you would have every date listed starting with Jan 1 and ending at Dec 31. You could have separate columns for each department, just a total column, or a combination thereof. That's up to you, but either way you would use a COUNTIFS with cross sheet references along the lines of

=COUNTIFS({Source Sheet Start Date}, @cell <= [Date Column]@row, {Source Sheet End Column}, @cell >= [Date Column]@row, {Source Sheet Dept Column}, @cell = "Dept. A")

The dept piece in italics above can be removed if you just want to show the totals.

ChristianFinke

@lmarantos

If I were you, I’d set up a helper sheet to track daily totals without needing 365 columns. You can create a column for each date in the year and use a formula like COUNTIFS([Start Date]:[Start Date], <= [Date], [End Date]:[End Date], >= [Date]) to count overlapping time-off requests. You’d just update it each year with new dates. Another option is to use a report to summarize entries by day and show those totals on a dashboard—super clean and easy to share. You could even filter by region or date range to give directors exactly what they need. Let me know if you want help with this!

Paul Newcome

You could use a separate sheet that has a single date column in it. In this column you would have every date listed starting with Jan 1 and ending at Dec 31. You could have separate columns for each department, just a total column, or a combination thereof. That's up to you, but either way you would use a COUNTIFS with cross sheet references along the lines of

=COUNTIFS({Source Sheet Start Date}, @cell <= [Date Column]@row, {Source Sheet End Column}, @cell >= [Date Column]@row, {Source Sheet Dept Column}, @cell = "Dept. A")

The dept piece in italics above can be removed if you just want to show the totals.

lmarantos

@Paul Newcome I'm going in the direction you suggested. Thank you so much for your suggestion!

Paul Newcome

Happy to help. 👍️