When working with date ranges, such as check-in and check-out dates, it’s often useful to calculate the number of nights spent in each month. This can help analyze current bookings or evaluate trends across specific periods.

While Google Sheets doesn’t have a built-in function for this exact calculation, you can achieve it by combining a few powerful formulas.

In this guide, we’ll walk through the steps to calculate the number of nights in each month from check-in and check-out dates in Google Sheets.

Calculate the Number of Nights from a Start and End Date in Google Sheets

Assume your check-in date is in cell A2 and the check-out date is in cell B2. If both dates fall within the same month, you can use the DAYS function to calculate the number of nights:

=DAYS(B2, A2)

Here, B2 represents the check-out (end) date, and A2 is the check-in (start) date.

For example, if the check-in date in A2 is 05/03/2025 and the check-out date in B2 is 08/03/2025, the formula will return 3, indicating a three-night stay.

Handling Dates in Different Months

If you’re unsure whether the dates span across different months, you’ll need to use separate formulas to account for each month.

To calculate the number of nights in the check-in month:

=IF(EOMONTH(A2, 0)<>EOMONTH(B2, 0), DAYS(EOMONTH(B2, -1), A2)+1, DAYS(B2, A2))

For the check-out month:

=IF(EOMONTH(A2, 0)<>EOMONTH(B2, 0), DAYS(B2, EOMONTH(B2, -1)+1),)

These formulas ensure accurate calculations, even when the dates span multiple months.

For example, consider the check-in date 28/12/2024 and the check-out date 02/01/2025. The first formula will return 4, representing the number of nights in December, while the second formula will return 1, representing the night in January. In total, there are 5 nights (4 nights in December and 1 night in January).

Calculate the Number of Nights in Each Month for Multiple Records

To process multiple check-in and check-out dates and generate a summary of nights per month, use this array formula:

=ArrayFormula(LET(_in, A2:A, _out, B2:B, start, HSTACK(EOMONTH(_in, -1)+1, IF(EOMONTH(_in, 0)<>EOMONTH(_out, 0), DAYS(EOMONTH(_out, -1), _in)+1, DAYS(_out, _in))), end, HSTACK(EOMONTH(_out, -1)+1, IF(EOMONTH(_in, 0)<>EOMONTH(_out, 0), DAYS(_out, EOMONTH(_out, -1)+1),)), QUERY(VSTACK(start, end), "SELECT Col1, SUM(Col2) WHERE Col1 IS NOT NULL GROUP BY Col1 LABEL Col1 'Month', SUM(Col2) 'Nights' FORMAT Col1 'MMM-YY'")))

• A2:A: The range for check-in (start) dates.
• B2:B: The range for check-out (end) dates.

This formula generates a summary with months in one column and the total number of nights in each month in the next.

Formula Breakdown

The formula uses LET to assign names to values or expressions and reuse those names in subsequent calculations. This reduces redundancy and makes the formula more readable and efficient.

Syntax of LET:

LET(name1, value_expression1, [name2, value_expression2, …], formula_expression)

Here are the names, their assigned values or expressions, and the final formula expression:

• _in: A2:A (the start date range).
• _out: B2:B (the end date range).
• start: HSTACK(EOMONTH(_in, -1)+1, IF(EOMONTH(_in, 0)<>EOMONTH(_out, 0), DAYS(EOMONTH(_out, -1), _in)+1, DAYS(_out, _in)))

This part calculates the number of nights in the month of the check-in date, with the first column displaying the month start dates corresponding to the check-in dates and the second column showing the calculated number of nights for the check-in month.

• end: HSTACK(EOMONTH(_out, -1)+1, IF(EOMONTH(_in, 0)<>EOMONTH(_out, 0), DAYS(_out, EOMONTH(_out, -1)+1), 0))

This part calculates the number of nights in the month of the check-out date, with the first column displaying the month start dates corresponding to the check-out dates and the second column showing the calculated number of nights for the check-out month.

Final Formula Expression: QUERY(VSTACK(start, end), "SELECT Col1, SUM(Col2) WHERE Col1 IS NOT NULL GROUP BY Col1 LABEL Col1 'Month', SUM(Col2) 'Nights' FORMAT Col1 'MMM-YY'")

• VSTACK(start, end): Combines the two data sets (start and end) vertically, creating a two-column array with:
  • Column 1: Month start dates.
  • Column 2: Number of nights.
• QUERY: Groups the combined data by month start dates, calculates the total nights per month, and formats the month start dates as MMM-YY.
  • The first column is labeled Month.
  • The second column is labeled Nights.

The formula provides the total number of nights spent in each month for the given check-in and check-out dates.

Resources

• Excel Tutorial: Calculate Nights Between Dates in Each Month
• Templates:
  • Reservation and Booking Status Calendar in Google Sheets
  • Hotel Room Availability and Booking Template in Excel