日期计算器输入天数计算日期_计算日期范围内的活动

日期计算器输入天数计算日期

Today's challenge is to count how many guests stayed at a hotel, in a specific date range, based on the guest arrival and departure dates.

今天的挑战是，根据客人的到达和离开日期，计算在特定日期范围内在酒店住宿的客人数量。

查找特定日期 (Find Specific Dates)

In previous examples, we've seen how to check if a specific date falls within a date range. For example, if you have a list of orders, you can use SUMIF or SUMIFS, to add up all the orders between a start and end date. You can see the written instructions for this on my website.

在前面的示例中，我们已经看到了如何检查特定日期是否在日期范围内。 例如，如果您有订单列表，则可以使用SUMIF或SUMIFS在开始日期和结束日期之间添加所有订单。 您可以在我的网站上看到有关此内容的书面说明 。

来宾参观 (Guest Visits)

It is a little trickier to count hotel guests though, because the booking table only shows the guest arrival and departure dates. We have to use those dates, to see if any part of a guest's visit overlapped our date range.

不过，对酒店客人进行计数比较麻烦，因为预订表仅显示客人的抵达和离开日期。 我们必须使用这些日期，以查看客人访问的任何部分是否与我们的日期范围重叠。

For this report, we want the number of guest who were in the hotel during the reporting period of December 7th to 9th. I've highlighted the records that should be included, when we create a formula to count the guests.

对于此报告，我们希望在12月7日至9日的报告期内入住酒店的客人数量。 当我们创建一个计算来宾数量的公式时，我已突出显示应包括的记录。

客人何时离开？ (When Did the Guest Leave?)

To calculate if the guest's visit was within the date range, we'll start with two short formulas.

为了计算客人的访问是否在日期范围内，我们将从两个简短的公式开始。

The first formula will check the guest's departure date – was it on or after the start of our date range? (If they left before the date range started, they won't be counted)

第一个公式将检查客人的出发日期–是在我们的日期范围开始时还是之后？ (如果它们在日期范围开始之前就离开了，则不会计算在内)

The report start date is in cell C3, and guest's departure date is in cell C6, so the formula is:

报告开始日期在单元格C3中，而客人的离开日期在单元格C6中，因此公式为：

=C6>=$C$3

= C6> = $ C $ 3

The first 5 guests all left before December 7th, so the result in those rows is FALSE.

前5位访客在12月7日之前全部离开，因此这些行的结果为FALSE。

客人什么时候到达？ (When Did the Guest Arrive?)

The second formula will check the guest's arrival date – was it on or before the end of our date range? (If they arrived after the date range ended, they won't be counted)

第二个公式将检查客人的抵达日期–是在我们的日期范围结束时还是之前？ (如果它们在日期范围结束后才到达，则不会计算在内)

The report start date is in cell D3, and guest's arrival date is in cell B6, so the formula is:

报告开始日期在单元格D3中，而客人的到达日期在单元格B6中，因此公式为：

=B6<=$D$3

= B6 <= $ D $ 3

Guest #11 arrived on December 10, and that is after the report end date of December 9th, so the result in that row is FALSE.

来宾＃11在12月10日到达，也就是在报告结束日期12月9日之后，因此该行的结果为FALSE。

两种结果都为真 (Both Results TRUE)

If the result of both formulas is TRUE, then the guest stayed at the hotel during the reporting period:

如果两个公式的结果均为TRUE，则在报告期间，客人在酒店住宿：

• they arrived on or before December 9th
  他们于12月9日或之前抵达
• they departed on or after December 7th
  他们于12月7日或之后出发

We'll use a SUMIFS formula to check those two columns, and sum then number of guests in column E. I've changed the column headings in F and G, to Dep Check and Arr Check.

我们将使用SUMIFS公式检查这两列，然后对E列中的来宾总数求和。我将F和G中的列标题更改为Dep Check和Arr Check 。

Enter this formula in cell F3:

在单元格F3中输入以下公式：

=SUMIFS(tblGuests[Guests], tblGuests[Dep Check],TRUE, tblGuests[Arr Check],TRUE)

= SUMIFS(tblGuests [Guests]，tblGuests [Dep Check]，TRUE，tblGuests [Arr Check]，TRUE)

得到结果 (Get the Result)

The result is 11, and that matches the total when the numbers are selected in the manually highlighted rows. You can see that automatic calculation in the Status Bar.

结果为11，与在手动突出显示的行中选择数字时的总数匹配。 您可以在状态栏中看到该自动计算。

下载样本文件 (Download the Sample File)

To see how the formulas work, you can download the sample file from my Contextures website. On the Excel Sample Files page, go to the Functions section, and look for FN0036 - Count Hotel Guests in Date Range.

若要查看公式的工作原理，可以从Contextures网站下载示例文件。 在“ Excel示例文件”页面上，转到“功能”部分，然后查找“ FN0036-在日期范围内计数酒店客人” 。

The zipped file is in xlsx format, and does not contain macros. It also has another, more complex, example of counting in a date range. Guests are counted by their hotel loyalty program level, and the number of nights booked in the date range is also calculated.

压缩文件为xlsx格式，不包含宏。 它还具有另一个更复杂的示例，用于在日期范围内进行计数。 根据他们的酒店忠诚度计划级别对客人进行计数，并计算日期范围内的预订住宿天数。

翻译自: https://contexturesblog.com/archives/2014/12/18/count-activities-in-a-date-range/

日期计算器输入天数计算日期