# Count days separated by month

Occasional Contributor

# Count days separated by month

Hi

Say I have table with 2 dates (field1 & field2)
20/12/2020 & 10/01/2021

How would I show the datediff by month in a query
e.g.
Dec 20 = 12
Jan 21 = 10

Or if the dates were 28/08/2021 & 02/10/2021
Aug 21 = 4
Sep 21 = 30
Oct 21 = 2

Thank you

5 Replies

# Re: Count days separated by month

Sorry but I can't make any sense of the values in your examples.

# Re: Count days separated by month

Sometimes it can help to explain the real world problem you need to solve, rather than trying to "simplify" by abstracting out just a few details.

Like Colin, I can't see any logic in the examples provided as raw numbers.

What is the goal? Why are you counting months, or is it days?

Thanks for providing enough context to help us offer suggestions.

# Re: Count days separated by month

Sorry if I didn’t give enough information – my bad.

I am creating a database for a friend who has a small hotel – I’m not getting paid, but have been promised a good dinner.

She has a number of rooms in her hotel.

The rooms rent out for different amounts – depending on demand.

She would like to know what the bookings were per month.

But this, obviously, has a problem as she may book out a room for (for example) 6 weeks, 2 weeks, 4 days, etc, etc. so on some occasion the bookings would go from one month to another.

So rather than using the start date (e.g. 25/12/2020 for X days) it would be better if she could know what the bookings where per month.

I hope this makes sense.

Using my original post’s details let say some booked a room from 28/08/2021 & 02/10/2021

She would want to know that the room was booked for:

Aug 21 = for 4 days

Sep 21 = for 30 days

Oct 21 = for 2 days

I have tried to create a query to do this but, let just say, I’m not have much luck.

Thanks

# Re: Count days separated by month

Thank you. Now that does make sense.

You need to have each booking recorded one of three ways.

Each booking could have a start date and and end date. I.e. a booking for three days starting on December 1st would have an end date of December 3.

Each booking could be recorded on each of the days in the time span, i.e. a booking starting on December 1st would have entries for that booking on 1/12, 2/12 and 3/12

It sounds like your approach is to record the Start date and a second field with the length of stay.  That's why you are having a problem with the calculation across month boundaries where you need to know "bookings" per month. A booking could be in two different months, or even three if it is for more than 30 (or 31) days.

I think you also need to pin down what you and your client mean by "bookings". Do you mean the number of times a room is booked for one day or longer? Or, do you mean the number of nights for which a room is booked? Slightly different meanings. Let's say 10 people each "book" the same room for two nights each. That's 10 "bookings" by the first definition, or 20 "bookings" by the second definition. Before we get to a specific suggestion, we do need to pin down that definition.

For me, the second approach mentioned above, one record per room per day it is booked, would probably be the easiest way to manage calculations like this, but it could be done with the other approach, as long as we know what we are counting exactly.

# Re: Count days separated by month

You can use the query DaysMonthsDateRange found in \demos\Date.accdb at

DaysMonthsDateRange

Id        DateStart      DateEnd    Year Month DateFrom      DateTo     Days

 0 28-08-2021 02-10-2021 2021 8 28-08-2021 01-09-2021 4 1 28-08-2021 02-10-2021 2021 9 01-09-2021 01-10-2021 30 2 28-08-2021 02-10-2021 2021 10 01-10-2021 02-10-2021 1

However, an end date is normally not included in date interval calculations so, to have a count of 2 for September, you would need to add one day to the date of your Field2 and pass that to DateEnd.

``DateEnd = DateAdd("d", 1, [Field2])``