Counting Days in Calendar View or in Sheet Between Date Ranges
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!
Lindsey Marantos, PhD
Talent Programs Manager, Total Rewards
Heartland Dental
Best Answer
-
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.
Answers
-
@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! -
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.
-
@Paul Newcome I'm going in the direction you suggested. Thank you so much for your suggestion!
Lindsey Marantos, PhD
Talent Programs Manager, Total Rewards
Heartland Dental -
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!