How can you return the days in a month between 2 dates?

Justin Mauzy

I have 2 columns in a sheet, Check-In Date and Check-Out Date.

On the 1st row in the image above, the Check-In Date to the Check-Out Date spans over 3 months.

Here is what I am trying to do:

Using the 2 dates 08/20/2022 and 10/15/2022 (Check-In Date and Check-Out Date)
- Count the days in August
- Count the days in September
- Count the days in October
Those results would go into 3 other columns:
- 1st month - value of August days
- 2nd month - value of September days
- 3rd month - value of October days

Any help would be greatly appreciated.

Find more posts tagged with

Accepted answers

All comments

Mike TV

@Justin Mauzy

This may need additional tweaking and troubleshooting for months such as Nov, Dec that are past the dates given in the Check-In and Check-Out dates. It also would probably need tweaking for a range such as a Check-In in December and a Check-Out in Jan/Feb the following year, etc. However, this shows you proof of concept and if you understand what I'm doing here you could probably get it working on your own.

Here's an example sheet:

You'll want to set up Sheet Summary cells like so and all as Date type columns:

August Days formula:

=IF(MONTH([Check-Out Date]@row) = 8, NETDAYS([August Start]#, [Check-Out Date]@row), IF(AND([Check-In Date]@row < [August Start]#, [Check-Out Date]@row > [August End]#), NETDAYS([August Start]#, [August End]#), IF(AND(MONTH([Check-In Date]@row) = 8, MONTH([Check-Out Date]@row) > 8), NETDAYS([Check-In Date]@row, [August End]#), NETDAYS([Check-In Date]@row, [Check-Out Date]@row))))

September Days formula:

=IF(MONTH([Check-Out Date]@row) = 9, NETDAYS([September Start]#, [Check-Out Date]@row), IF(AND([Check-In Date]@row < [September Start]#, [Check-Out Date]@row > [September End]#), NETDAYS([September Start]#, [September End]#), IF(AND(MONTH([Check-In Date]@row) = 9, MONTH([Check-Out Date]@row) > 9), NETDAYS([Check-In Date]@row, [September End]#), NETDAYS([Check-In Date]@row, [Check-Out Date]@row))))

October Days formula:

=IF(MONTH([Check-Out Date]@row) = 10, NETDAYS([October Start]#, [Check-Out Date]@row), IF(AND([Check-In Date]@row < [October Start]#, [Check-Out Date]@row > [October End]#), NETDAYS([October Start]#, [October End]#), IF(AND(MONTH([Check-In Date]@row) = 10, MONTH([Check-Out Date]@row) > 10), NETDAYS([Check-In Date]@row, [October End]#), NETDAYS([Check-In Date]@row, [Check-Out Date]@row))))

Mike TV

@Justin Mauzy

After I posted that I realized I didn't include if both start and end dates are same month. These formulas don't work for that. You'll also need to solve for counting 0 days if for example the start and end dates are both in Sept so that August shows 0. However, if you understand what's going on with the basic concept here you can fix it up and get it working.

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.