Overlapping Days
By Richard Rost
104 minutes ago
Calculate Overlapping Days Between Two Date Ranges
In this lesson, we will calculate overlapping days between two date ranges in Microsoft Access. I will show you how to find the later start date and earlier end date, return zero when ranges do not overlap, and count days using inclusive or exclusive endpoints. We will create a reusable VBA function, test it with several date-range scenarios, and correct a common copy-and-paste comparison error.
Timothy from Sioux Falls, South Dakota (a Platinum Member) asks: I'm building a database to split utility bills between tenants who move in and out. I have two different date ranges, and I need to know how many days they overlap. Is there an easy way to calculate that in Microsoft Access?
Members
In the extended cut, we will build a utility bill calculator that loops through each billing-period day, determines which tenants were active, splits the daily cost among them, and totals what each tenant owes. We will work with recordsets, loops, updating table data, move-in and move-out dates, and a query to show each tenant's amount due.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Links
Recommended Courses
Keywords
TechHelp Access, Access VBA date range overlap, calculate overlapping days, overlapping date ranges, date range intersection, inclusive date range, VBA date math, reservation conflict detection, rental billing period, utility bill tenant split, DateDiff alternatives
More InformationTranscript Do you have two date ranges in your Access database and need to know how many calendar days they have in common? Maybe it's reservations, rentals, employee time off, or just two dates that refuse to play nicely together.
Welcome to another TechHelp video brought to you by Access Learning Zone. I'm your instructor, Richard Rost.
Today, we're going to calculate overlapping days between two date ranges in Microsoft Access. It's one of those little date math problems that seems simple until you run into ranges that don't overlap at all, barely touch, or one range completely contains the other.
By the end of this video, you'll have a small reusable VBA function that can handle those situations cleanly and give you the number of shared days whenever you need it in your database.
Today's question comes from Timothy in Sioux Falls, South Dakota, one of my Platinum members. Timothy says, "I'm building a database to split utility bills between tenants who move in and out. I have two different date ranges, and I need to know how many days they overlap. Is there an easy way to calculate that in Microsoft Access?"
Well, this is one of those problems that seems easy until you take a deeper look at it. At first glance, you might just think, "I'll just use DateDiff." Well, DateDiff can't tell you which two dates to compare until we first figure out where the overlap begins, or if there is one, and where it ends.
Now, the good news is that this isn't one of those giant spaghetti code problems. Once you understand the simple rule, you can put it in a small VBA function and use it anywhere in your database.
Now, before we write a single line of code, let's picture what can happen. Suppose range one goes from January 13 through January 17. This purple one right there. All these other ones represent range two, and this is the start date and the end date for range one.
Now, range two might run from January 9 until the 11th, this guy. Now, these two ranges, this one and the purple one, don't overlap at all, just like this guy over here, 19 to the 21st. There's zero overlap.
Or you might have a situation where range two starts from the 11th and goes to the 19th. This one completely encompasses this one, so all of these days overlap.
Or you might have situations like these, where they start before and during, or start during and after. Or you can have ones that just completely run during the other one.
So these are all different situations you have to take into consideration. And they look like different cases, and technically they are, but we don't need four different chunks of VBA code. That's how little bugs breed when no one's paying attention.
We want one rule that handles every situation. One rule to rule them all. One rule to rule them all. I just came up with that. That's pretty cool.
So here's the trick. Here's the rule. Find out where the overlap starts. Look at both starting dates and pick the later one.
So if it's this starting date versus this starting date, pick the later one. If it's this starting date versus this starting date, pick the later one.
Why? Because two ranges can't both be active until the later range has started. If this is the later range, then this is the soonest that both of these could have started.
Then define where the overlap ends. Look at the ending dates and pick the earlier one. So find this guy's ending date and this guy's ending date, and that's the earlier one.
Why? Because as soon as either range ends, the shared time is over. So it doesn't matter if it's this one and this one, this one and this one, this one and this one, whatever. Find the later start time and the earlier end time. It's like math and things.
But now we need one quick safety check. What if range one ends on January 10th and range two doesn't begin until January 15th? Well, our calculated overlap start would be January 15th, but our calculated overlap end would be January 10th. And so the end is before the start.
So there's zero shared days, so return zero. So we have to put that into there. Otherwise, we just count the days.
But there's still one little detail that gets a lot of people. The difference between January 8 and January 10 is two, but that number counts the number of day boundaries crossed. If you go 8, then 9, then 10, it's like calculating hotel room stays.
But if this is a reservation or a rental that includes both endpoints, January 8th and January 10th, like figuring out utility bills, like Timothy is doing, the actual days that you count are going to be the eighth, ninth, and tenth. That's three days.
So if your range is an inclusive date range, we add one. If your business rule is excluding those end dates, then you don't add one. So you have to decide which one you're going to do and apply it consistently.
And then we've got the algorithm, and it looks worse than it is. It's not that hard once you see it in code.
We're going to make a function called OverlapDays. It's going to receive four date values: start one, end one, start two, and end two. We'll use two local variables, overlap start and overlap end.
Then we'll use an If statement to pick whichever start date is later and whichever end date is earlier. And if the overlap end is less than the overlap start, the ranges miss each other, so the function returns zero.
Now, you could use DateDiff here, but since we're dealing with days, there's no need for DateDiff. You can just use basic date math. Remember, in Access, a value of one for a date is one day. So use DateDiff for the weird stuff, like adding months or whole years or things like that, and then use date math.
Before we get started coding, this is a developer-level video. So if you've never done any VBA programming before, go watch this video. It'll get you started and teach you everything you need to know in about 20 minutes.
You'll need to know how to create a function that returns a value. If not, go watch this video. Here's that date math I was talking about earlier. You want to add a week, just add seven. You want to subtract three days, just subtract three.
Now, we don't need it for this video, but if you want to learn about DateDiff, go watch this one. These are all free videos. They're on my YouTube channel. They're on my website. Go watch any of those that you want and come on back. I'll wait for you. Go on, you're holding up the class. Go get.
Here I am in my TechHelp free template. This is a free database. You can grab a copy from my website if you want to, but any database will do.
Now, I'm going to take this form, and we're going to put four text boxes on it for our range start and end dates. Let's just move this thing down a little bit out of the way here.
We're going to take this one. We're going to call this one range one. I'm going to slide it over to the left just a wee bit. And this guy, normally this is a text box that shows the current date. That's why it's got =Date() in the control source.
We're going to get rid of that so we can type in a value. If you leave that in the control source, it's always stuck on that value. And I'm going to name this guy. Let's call this Start1.
Let's make this a little bit bigger here. Let's copy and paste. This will be range two. You will be called Start2. No spaces.
Then I'm going to copy again, copy and paste. Now this guy, we can get rid of your little label there and slide it up here. This is going to be End1.
Then copy and paste again. There we go. Right nicely there. And you'll be, guess what? Anybody? Bueller? Bueller? End2.
And so I don't have to keep typing in dates, I'm going to select all of them, and I'm going to make the default value =Date(). What's the difference? Well, if it's in the control source, it's set. You can't change it. If it's in the default value, it just starts with that, but you can change it. That's the big difference there.
Save it, close it, open it. I've got a little button here to open up my main menu. And there we go. There's some dates in there.
Now, down here I've got a button that says Hello World. What does this guy do? Well, this uses my Status function to just put Hello World in this box. And if you're not familiar with my Status function, I use that instead of MessageBox.
It's a whole lot easier just to put some text in a text box on your form. Status, the Status function. It just looks like this: set the status box equal to whatever I send into it.
If you want a video for it, here you go. I know everyone always asks, "What's that Status thing?" Sorry, I should have put this in the prerequisites. I always forget.
But what we're going to do is, I'm just going to hijack this button in a minute. But before we do that, let's make a function to calculate our overlap days.
So, Public Function OverlapDays. This way, anybody can use it. OverlapDays is the name. And then we want to take four bits of information: Start1 As Date, End1 As Date, Start2 As Date, and End2 As Date.
And I don't really like where that line break ended up, and this End Function comes way over there. Oh, and someone's beaming in. Let's do it like that. Let's keep these guys together like that. Yeah, it's nitpicking, but that looks so much better.
Now we'll need two variables in here to calculate the start overlap and the end of the overlap. So, Dim OverlapStart As Date and OverlapEnd As Date.
Now we've got to calculate those. So let's find the later starting date. If Start1 is greater than Start2, then OverlapStart equals Start1. Otherwise, OverlapStart equals Start2. It's that simple.
Now we know which one of those start dates is the later start date. And we'll do the same thing for the end dates. So we'll just copy this block here and paste it, and we'll find the earlier ending date.
And now we just have to change Start to End. So, if End1 is greater than End2, then OverlapEnd equals End1. Otherwise, OverlapEnd equals End2.
Now we know the later starting date and the earlier ending date. Everybody with me? Raise your hand if you're not. Again, you're holding up class. What are you doing?
Oh, one more thing I forgot. Up here, put As Long. We want to make sure this is returning a Long. Otherwise, you get a Variant, but that doesn't matter. Just make sure you put As Long here. We're getting a Long integer back.
Continuing on. Now we've got to find the overlap. If there's no overlap, then we have to deal with that first. So, if OverlapEnd is less than OverlapStart, then OverlapDays equals zero. That means the earlier ending date is less than the later starting date, so they don't overlap at all.
Otherwise, there's going to be some overlap. OverlapDays equals OverlapEnd minus OverlapStart. And this is where you put in that plus one if it's inclusive. Add one if endpoints are inclusive.
So if you're not charging them for that last day, like if you're doing hotel reservations, you check in on Monday, you check out on Wednesday. If you don't charge them for Wednesday night, then you don't add that one because they're only being charged for two nights.
But if it's a daily rental, from this day to that day, then you do. Or if you're calculating move-in and move-out dates for utilities, then you would want to put that on there.
And If, and that's it. There's your function. Now we've got a function we can use. And we can move this to a global module if you want anybody in the database to be able to use it.
Let's cut this whole thing out. Cut, snip. And now I'm going to go over here, go to my global module. If you don't have one of these, just go to Create and then Module, not Class Module, Module. But I already have one, so I'm going to use this guy.
And then I'm just going to come down to the bottom and paste it in here. Now I can use this public function, OverlapDays, anywhere in my database.
But let's go back. Let's save it. Oh, we forgot. Debug Compile once in a while. Don't forget that. Everything compiled.
I'm going to now close this window right here, this guy that will close that module and put me back in the form module. And now I'm going to hijack my Hello World button click.
Instead of statusing Hello World, we're going to say, Dim L As Long. L equals OverlapDays. Now, what are we sending it? We have these same names as fields on this form, as text boxes here: Start1, End1, Start2, End2.
They just happen to have the same names. These could be named anything differently. These don't have to be Start1 or whatever. This could be X, Y, L, P, and Q, whatever. We just happen to name them the same thing. That's just a fluke.
This could be RentalStartDate. But since they are the same names, it doesn't matter: Start1, End1, Start2, End2.
Now, what's the value that you got back? Well, we're going to status it. Status. See how this is better than MessageBox? Status, overlap is going to be L days.
And yeah, you can get crazy. And if it's just one, get rid of the S. I'm not doing that today. I've got other videos where I cover that.
Debug Compile once in a while. Let's close it, save it, open it. I'm just going to click the button. Overlap: one day. Because it's one day. The fourth is one day. If it's the fourth to the fourth, because we added one.
Let me make this a little bit bigger so it's easier to read, and change this from Hello World to Calculate Overlap. Let's put this over here, make it bigger. Let's do a little bit so we can actually read it, maybe 16-point.
Here we go. Save it, close it, open it again. Let's do 8/1, and this guy is from the third to the tenth. Calculate Overlap: eight days.
And, oh, wait a minute, that's not right. Overlap: eight days. That's not right.
Now, it took me all of three seconds to find that I have a bug in here. And I honestly thought about re-recording the video, but I wanted to leave it in. If you caught the bug, if you caught the mistake while I was doing it, then give yourself a round of applause.
This is exactly what happens when you copy and paste code and don't carefully read it. Let's take a look at what I did here. Tell me if you can see it.
We have to find the later starting date, but we have to find the earlier ending date. And I just copied and pasted this block and changed Start to End. But I forgot to flip the sign in my original code.
See, everyone, I was taking notes and I was writing it up earlier. I have it correct, and it worked for me when I demoed it before. But I wasn't paying attention, and I just copied and pasted this.
This is the danger of copying and pasting code that you don't understand, whether it's from older code you wrote yourself, or code you got from AI, or you downloaded from some web page somewhere.
I don't like doing that. I shouldn't have done that. I always like to write my own code as I'm going along because if I was doing this in my brain, thinking to myself, "Find the earlier ending date," if End1 is less than End2, that's the overlap end date.
If I didn't copy and paste that, I wouldn't have made that mistake. And I did, and I'm leaving it in the video because it's a teaching moment. I make this mistake myself from time to time.
And as soon as I came back and looked at it, I'm like, "Wait, oh, yeah, there it is right there."
Now let's go back and hit the button again. Let me save it. Debug Compile, close it. And yes, I've been doing this for 30 years, and I still make little stupid mistakes like that from time to time.
Now, if I hit it, oh, there we go. Two days. That's what I was expecting. The third and the fourth are overlap days.
Let's try another one. Let's go back and look at my ranges here that I've got. Range one is always the 13th to the 17th. I'll try to get that little guy on their side by side.
So what do we have here? Here's the 13th to the 17th. Let's try this big one here. So, the 11th to the 19th. Overlap. All five days: one, two, three, four, five.
How about this guy over here, the 9th to the 11th? 9 to the 11th. Zero days. No overlap there.
How about this one? 19th to the 21st. 19th to the 21st. No overlap.
How about this one? Starts before the start date and ends during. This would be the 11th to the 14th. And that's got two overlapping days. One, two.
How about the 16th to the 19th? We're testing all the cases here. 16th to the 19th. Two days, beautiful.
And some durings. How about 14 to 16? 14 to 16? Three days, yep. One, two, three.
One more. 13 to the 16th. 13 to the 16th. Four days.
Wait, oh no, I typed in 13 to the 16th. This should be 13 to the 15th. Three days, perfect.
I'm pretty satisfied. And yes, I made this in Excel, if you can tell. And no, this is not PowerPoint. I used to use PowerPoint. I built myself a database to do this, though. All my slides are now built in an Access database.
One of these days, maybe I'll show you guys how I did it. But I can easily come in here and click on stuff and change it and move these pictures around and all kinds of cool stuff.
Anyways, so that's how you check for overlaps. Now, once you have this handy little function, you're probably going to find uses for it all over the place.
Reservation systems: you can compare new requested reservations against an existing reservation and determine whether they conflict.
In a rental database, you can calculate how many rental days fall inside a particular billing period.
You might compare employee dates with a payroll period. For warranties, compare a repair date with the warranty start and end dates, all kinds of stuff you can do.
For subscriptions, you can compare the active subscription period with the monthly reporting period.
Now we learned how to calculate the overlap between two date ranges. And that's a handy little function you're going to use in lots of different places.
In the extended cut for the members, we're going to solve the real-world problem that inspired this video. We're going to build a simple utility bill calculator that loops through each day of the billing period, figures out who was living in the property on that day, splits that day's cost among the active tenants, and keeps a running total of what each person owes.
So, along the way, we're going to work with recordsets, loops, updating data in a table, and by the end, you're going to have a complete working solution that you can adapt to your own databases.
Here it is. We've got the customer table. We're going to add a move-in date and a move-out date, and then an amount due field. So we know when they moved in and when they moved out.
Now, on the main menu, we're going to have a date range for whatever the utility bill is, or an electric bill, gas, whatever, Netflix, the amount due. And then we're going to click Calculate, and it's going to run through every day in the billing period and add up who owes money for each day.
Because if Richard didn't move in until the fifth, he doesn't owe for the first, second, third, and fourth. So then you can open up a query, and it'll show you who owes how much for that billing period.
And that's all covered in the extended cut for the members. Silver members and up get access to all of my extended cut videos, not just this one, all of them. And Gold members can download these databases, and everybody gets some free training classes, and it's wonderful and merry.
So we'll click that blue Join button today.
And also, if you want to dig deeper, in my Access Developer 24 class, we cover building a complete reservation system. And this also includes reservation conflicts. So if two people want to reserve the same thing on the same dates, it says, "I can't do it, someone's already got it." So check this one out, too.
As a review, with any two date ranges, find the later starting date and the earlier ending date. If the ending date is before the starting date, the ranges don't overlap, and the answer is zero.
If they do overlap, count the difference. Use either DateDiff or basic date math. If you're including the endpoints, add one. If not, don't add one.
And once you understand the math, everything becomes a whole lot less mysterious.
But there you go, folks. That is your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time, and members, I'll see you in the extended cut.
If you enjoyed this video, hit that thumbs-up button right now and give me a like. Also, be sure to subscribe to my channel, which is completely free, and make sure you click that bell icon and select All to receive notifications whenever I post a new video.
If you're new to Microsoft Access, check out my Access Beginner Level One course. It's over four hours long, and it covers all the basics, like tables, queries, forms, and reports. It's a great place to start, and it's also completely free.
Members of my channel get extended cut videos, sample databases, access to my Code Vault, and full training classes every month. Click the Join button for details.
Thanks for watching. I'm Richard Rost with AccessLearningZone.com. Live long and prosper, my friends. I'll see you next time. Intro In this lesson, we will calculate overlapping days between two date ranges in Microsoft Access. I will show you how to find the later start date and earlier end date, return zero when ranges do not overlap, and count days using inclusive or exclusive endpoints. We will create a reusable VBA function, test it with several date-range scenarios, and correct a common copy-and-paste comparison error. Quiz Q1. What is the first step in determining the overlap between two date ranges?
A. Find the earlier of the two start dates
B. Find the later of the two start dates
C. Add both start dates together
D. Subtract the first start date from the second start date
Q2. What is the second step in determining the overlap between two date ranges?
A. Find the later of the two end dates
B. Add one day to both end dates
C. Find the earlier of the two end dates
D. Ignore the end dates
Q3. If the calculated overlap end date is before the calculated overlap start date, what should the function return?
A. -1
B. 1
C. Null
D. 0
Q4. Why is the later start date used as the overlap start date?
A. Both ranges cannot be active until the later range begins
B. The earlier range is always invalid
C. Access requires dates to be sorted in descending order
D. The later date always belongs to Range 2
Q5. Why is the earlier end date used as the overlap end date?
A. It makes DateDiff run faster
B. The shared period ends as soon as either range ends
C. The first range must always end first
D. It prevents dates from being stored as text
Q6. For inclusive date ranges, how many overlapping days are there from January 8 through January 10?
A. 1
B. 2
C. 3
D. 4
Q7. Why is 1 added to the date subtraction result for an inclusive date range?
A. Because Access date values start at 1
B. Because both the start date and end date are counted
C. Because DateDiff always returns zero
D. Because the overlap must include the next day
Q8. Which expression correctly calculates inclusive overlapping days after overlap dates have been determined?
A. OverlapStart - OverlapEnd + 1
B. OverlapEnd + OverlapStart
C. DateDiff("m", OverlapStart, OverlapEnd)
D. OverlapEnd - OverlapStart + 1
Q9. If a hotel guest checks in Monday and checks out Wednesday, and Wednesday night is not charged, which approach is generally appropriate?
A. Do not add 1 for inclusive endpoints
B. Add 1 for inclusive endpoints
C. Count Thursday as well
D. Return zero because the dates are different
Q10. What VBA data type is appropriate for a function that returns a whole number of overlapping days?
A. String
B. Boolean
C. Long
D. Date
Q11. Which comparison correctly finds the earlier of two end dates?
A. If End1 > End2 Then OverlapEnd = End1
B. If End1 < End2 Then OverlapEnd = End1
C. If End1 = End2 Then OverlapEnd = Start1
D. If End1 <> End2 Then OverlapEnd = End2
Q12. Where should a Public OverlapDays function be placed if it needs to be used throughout the database?
A. In a table
B. In a report's Record Source property
C. In a form text box Control Source
D. In a standard global module
Q13. In Access date math, what does subtracting one date from another generally return?
A. The number of days between the dates
B. The number of months between the dates
C. A formatted date string
D. The number of records in a table
Q14. Which situation is a useful application for an overlap-days function?
A. Checking whether a requested reservation conflicts with an existing reservation
B. Renaming a table automatically
C. Changing a form's background color
D. Importing a text file
Answers: 1-B; 2-C; 3-D; 4-A; 5-B; 6-C; 7-B; 8-D; 9-A; 10-C; 11-B; 12-D; 13-A; 14-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks. Summary Today's video from Access Learning Zone covers how to calculate the number of overlapping calendar days between two date ranges in Microsoft Access.
This is useful in many different kinds of databases. You might need to compare reservation dates, rental periods, employee leave dates, subscription dates, warranty coverage periods, billing cycles, or tenant move-in and move-out dates. In each case, the goal is the same: determine how many days two date ranges have in common.
The basic idea may seem simple at first. You might think that DateDiff can solve the problem immediately. However, DateDiff only calculates the difference between two dates. Before I can calculate that difference, I need to determine whether the ranges overlap at all, where the overlap begins, and where it ends.
Consider one date range running from January 13 through January 17. A second range could fall entirely before it, entirely after it, partially overlap it from the left or right, occur completely inside it, or completely contain it. These situations may look different visually, but they can all be handled with one consistent rule.
First, find the beginning of the overlap by comparing both start dates and selecting the later date. The two ranges cannot both be active until the later range has started.
Next, find the end of the overlap by comparing both end dates and selecting the earlier date. As soon as either range ends, the shared time is over.
In other words, the overlap begins at the later start date and ends at the earlier end date.
There is one important safety check. If the calculated overlap end date is earlier than the calculated overlap start date, then the ranges do not overlap. In that situation, the result should be zero days.
For example, if one range ends on January 10 and the other range does not begin until January 15, the calculated overlap start would be January 15 and the calculated overlap end would be January 10. Since the end comes before the start, there is no overlap.
Another important consideration is whether your dates are inclusive. If a date range runs from January 8 through January 10 and both dates count, then the range includes January 8, January 9, and January 10. That is three calendar days.
Basic date subtraction between January 8 and January 10 returns two because it measures the number of day boundaries between the dates. If you need both endpoints included, you must add one to the result.
Whether you add one depends on your business rules. For example, a hotel stay beginning Monday and ending Wednesday is often billed as two nights because the guest checks out Wednesday morning and is not charged for Wednesday night. In that situation, you would not add one. However, if you are allocating utility costs between tenants based on move-in and move-out dates, you may want both dates included.
The reusable solution is a VBA function named OverlapDays. The function accepts four Date values: the start and end dates for the first range, and the start and end dates for the second range. It returns a Long integer representing the number of shared days.
Inside the function, I use two Date variables: one for the overlap start date and one for the overlap end date.
To calculate the overlap start, compare the two starting dates and store the later one. To calculate the overlap end, compare the two ending dates and store the earlier one.
Be careful when writing the comparison for the ending dates. When determining the start of the overlap, I want the later date. When determining the end of the overlap, I want the earlier date. It is easy to copy and paste the first comparison and forget to reverse the logic for the second one. That small mistake can produce incorrect results.
This is a good reminder that copied code should always be reviewed carefully. Whether the code comes from an older project, a website, AI, or something you wrote earlier, make sure you understand what it is doing before relying on it.
Once the later start date and earlier end date have been calculated, compare them. If the overlap end date is earlier than the overlap start date, return zero. Otherwise, subtract the overlap start from the overlap end. If your dates are inclusive, add one to that result.
Because Access stores dates as numeric values, simple date math works well when you are working with days. A difference of one represents one day. DateDiff is still useful for more complicated calculations involving months, years, or other date intervals, but it is not necessary for this particular calculation.
I recommend placing the OverlapDays function in a standard global module rather than keeping it inside a form module. A Public function in a standard module can be used anywhere in the database, including forms, reports, queries, and other VBA procedures.
For testing, I set up four unbound text boxes on a form representing the two date ranges: Start1, End1, Start2, and End2. I gave each control a Default Value of Date() so that the controls initially display today's date while still allowing me to enter different values.
This is different from placing Date() in the Control Source property. If Date() is in the Control Source, the control is calculated and cannot be edited normally. If Date() is in the Default Value property, it only provides an initial value.
A button on the form can call the OverlapDays function, pass in the four date values from the form controls, and display the result. This makes it easy to test different date combinations.
For example, if one range runs from August 1 through August 4 and the other runs from August 3 through August 10, the overlapping dates are August 3 and August 4. With inclusive dates, the result is two days.
If the first range runs from the 13th through the 17th and the second range runs from the 11th through the 19th, the first range is fully contained within the second. The overlap is all five days of the first range.
If the first range runs from the 13th through the 17th and the second range runs from the 9th through the 11th, there is no overlap. The result is zero.
Likewise, if the second range runs from the 19th through the 21st, it occurs entirely after the first range, so the result is also zero.
If the second range runs from the 11th through the 14th, the shared days are the 13th and 14th, for a total of two days.
If the second range runs from the 16th through the 19th, the shared days are the 16th and 17th, again producing two days.
If the second range falls entirely within the first range, such as the 14th through the 16th, then all three of those days overlap.
Once you have this function available in your database, you can use it in many situations.
In a reservation system, you can compare a requested reservation period against an existing reservation to determine whether there is a scheduling conflict.
In a rental database, you can determine how many rental days fall within a particular billing period.
For payroll or employee management, you can compare leave dates, work dates, or employment dates against a payroll period.
For warranties, you can compare a repair date or service period against the warranty start and end dates.
For subscriptions, you can determine how many active subscription days fall within a monthly reporting period.
The key rule is straightforward. Given two date ranges, find the later starting date and the earlier ending date. If the ending date is before the starting date, there is no overlap and the result is zero. Otherwise, calculate the difference between those dates and add one if both endpoints should be included.
Also, in today's Extended Cut, we will build a practical utility bill calculator based on this same concept. We will add move-in and move-out dates to customer records, enter a billing period and total utility amount, and calculate how much each tenant owes based on the days they occupied the property.
The Extended Cut solution will loop through every day in the billing period, determine which tenants were active on each day, divide that day's cost among the active tenants, and maintain a running total for each person. Along the way, we will work with recordsets, loops, table updates, and a query that displays the amount owed by each tenant.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends. Topic List Calculating overlapping days between date ranges
Finding the later start date of two ranges
Finding the earlier end date of two ranges
Handling date ranges with no overlap
Inclusive versus exclusive date range counting
Creating an OverlapDays VBA function
Using date subtraction to count overlapping days
Making a public VBA function in a standard module
Testing date range overlap scenarios Article When you need to compare two date ranges in Microsoft Access, a common question is how many calendar days the ranges have in common. This is useful for reservations, rentals, employee leave, utility billing, subscription periods, warranties, and many other situations.
The key is to treat each range as having a start date and an end date. For example, one range might run from January 13 through January 17, while another might run from January 11 through January 19. In that case, every day from January 13 through January 17 overlaps, so the result is five shared days.
There are several possible relationships between two date ranges. They might not overlap at all. One range might completely contain the other. One might begin before the other and end during it. Or both dates of one range might fall completely inside the other range. Although these situations look different, they can all be handled with the same simple rule.
First, find the beginning of the overlap. Compare the two start dates and choose the later one. The two ranges cannot overlap before both ranges have started, so the later start date is always the first possible shared date.
Next, find the end of the overlap. Compare the two end dates and choose the earlier one. As soon as either range ends, the shared period is over, so the earlier end date is always the last possible shared date.
For example, suppose the first range is January 13 through January 17 and the second range is January 11 through January 19. The later start date is January 13, and the earlier end date is January 17. The overlap is therefore January 13 through January 17.
You must then check whether an overlap actually exists. If the calculated overlap end date is earlier than the calculated overlap start date, the ranges do not overlap. For example, if one range ends on January 10 and the other does not begin until January 15, the calculated overlap start would be January 15 and the overlap end would be January 10. Since the end comes before the start, there are zero shared days.
If the overlap end date is the same as or later than the overlap start date, you can calculate the number of days between them. Access stores dates as numbers, where one whole number represents one day, so simple date subtraction can be used for day-based calculations.
You also need to decide whether your date ranges are inclusive or exclusive. An inclusive date range counts both the start date and the end date. For example, January 8 through January 10 includes January 8, January 9, and January 10, for a total of three days. Basic date subtraction gives a difference of two, so you add one when both endpoints should be counted.
Inclusive ranges are common when calculating utility charges, occupancy, daily rentals, leave time, or any situation where a person is considered active for the entire start and end dates. If someone moves in on the 8th and moves out on the 10th, they may be responsible for utilities on all three dates.
Exclusive end dates are common in situations such as hotel stays. A guest checking in Monday and checking out Wednesday typically stays for two nights, Monday night and Tuesday night. Wednesday is the checkout date, not another charged night. In that type of system, you would not add one to the date difference.
A reusable Access function for this task should receive four date values: the first range's start and end dates, and the second range's start and end dates. It should determine the later start date, determine the earlier end date, check whether the end is before the start, and return zero if there is no overlap. Otherwise, it should return the number of days between the two overlap dates, adding one only if your business rules treat both endpoints as inclusive.
For example, consider a range from January 13 through January 17. A second range from January 9 through January 11 has no overlap because it ends before the first range begins. A second range from January 11 through January 19 overlaps the entire first range, producing five inclusive days. A second range from January 11 through January 14 overlaps January 13 and January 14, producing two inclusive days. A second range from January 16 through January 19 overlaps January 16 and January 17, also producing two inclusive days.
Once you create this logic as a reusable function, you can call it from forms, queries, reports, or other VBA procedures. In a reservation system, it can help determine whether a requested booking conflicts with an existing booking. In a rental database, it can calculate how many rental days fall within a billing period. In an employee database, it can determine how many leave days fall within a payroll cycle. In a utility billing system, it can help calculate how many days each tenant occupied a property during a billing period.
The overall process is always the same: choose the later start date, choose the earlier end date, return zero if the end is before the start, and otherwise calculate the number of days according to whether your dates are inclusive or exclusive. Primary Topics date range overlap logic, Access VBA function, later start date selection, earlier end date selection, no-overlap detection, inclusive versus exclusive date ranges, date arithmetic Secondary Topics public functions in standard modules, form button event code, debugging copied code, Debug Compile, DateDiff versus subtraction |
