< Go Back

Calculate date overlap in days

Do you want to learn how to calculate date overlap in days? Reading this post will teach you how to calculate date overlap in days, so you can do your own datasheet to manage your time with ease using some basic formulas in Excel.

Formula

The general formula is:

=MAX(MIN(end1,end2)-MAX(start1,start2)+1,0)

Explanation

Excel reads dates as numbers and using this formula you are ordering excel to do some simple arithmetic calculation using two numbers that represent dates.

The end1 part of the formula refers to the end of the interest period and the start1 refers to the start of the interest period, while the end2 and start2 represent the dates used to calculate date overlap in days.

The MIN part of the formula is used to get the earliest end date while the second MAX is used to get the latest end date. The MAX function at the start of the formula and the “;0” at the end is used to change all negative results to 0.

Example

If we take a look at the image shown below we can see this formula. Note that the commas were turned into semicolons. If it doesn’t work with semicolons you can try with commas:

=MAX(MIN(G7;C6)-MAX(G6;B6);0)

The way this formula works is by taking the numbers that represent as latest end date as result of formula part MAX(G6,B6) and then subtracting it from the number that represents as earliest end date as result of formula part MIN(G7;C6), the result is a number that represents date overlap in days.

Figure 1. Calculate date overlap in days example data sheet

Figure 2. Results from D11 without first MAX Function in the formula to calculate Date Overlap in Days

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar