How to Tally Days with an Date Entries

I have a form set up for people to enter their CHECK-IN and CHECK-OUT dates. I'm looking for a way to have the dates tallied for all reservations received. So for row 1 - a "1" would enter under 7/21, 7/22, 7/23 and 7/24. And for row 2 - "1" would enter in 7/21, 7/22, 7/23. etc. to 7/26.

Is there a way to do this so I don't have to manually do it. And it updates when I make a change to the check-in or check-out date.

Find more posts tagged with

Formulas

Automations

Accepted answers

Paul Newcome

In that case, you can skip the individual columns.

In a separate sheet, insert a date type column (called "Dates" in this example) and enter all of the dates you want displayed.

Then in a text/number column, the formula to get your counts would be:

=COUNTIFS({Reference Sheet C-In Date}, @cell <= Dates@row, {Reference Sheet C-Out Date}, @cell >= Dates@row)

All comments

Paul Newcome

What is the end goal for this? Are you wanting to eventually just show a total count by date?

KelJoy

The end goal is to know how many rooms are booked each night. I will have another grid that counts the all the "1" for each day and then display them in a report/dashboard.

So in the example provide…

7/21 totals 3

7/22 totals 3

etc.

7/25 totals 2

7/26 totals 2

Paul Newcome

In that case, you can skip the individual columns.

In a separate sheet, insert a date type column (called "Dates" in this example) and enter all of the dates you want displayed.

Then in a text/number column, the formula to get your counts would be:

=COUNTIFS({Reference Sheet C-In Date}, @cell <= Dates@row, {Reference Sheet C-Out Date}, @cell >= Dates@row)

KelJoy

Fantastic! - Now what if I need to break it down by the room type. Here are the reservation requests

And then I want the total in the corresponding column in the count grid:

Paul Newcome

FOr that, you can just add another range/criteria set to the COUNTIFS.

=COUNTIFS({Reference Sheet C-In Date}, @cell <= Dates@row, {Reference Sheet C-Out Date}, @cell >= Dates@row, {Reference Sheet Room Type}, @cell = "Run of House")

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.