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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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

  • ChristianFinke
    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!

    CHRISTIAN FINKE

    cfinke@digitalradius.com

    Schedule a Meeting

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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.

  • lmarantos
    lmarantos ✭✭✭
    edited 11/21/24

    @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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!