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.
The general formula is:
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.
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:
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