Calculating Date difference excluding specific dates

So someone asked me, what if we need difference between two given dates excluding some specific dates in between the range.

Scenario : Need to find number of months between any two given dates , however in calculating difference it should not consider month of April.

Solution: To solve this we would be using two formulas :- Datedif( ) and Sumproduct ( ).

This solution will be dynamic enough that later if required we can calculate number of days or number of years between any two given dates and excluding some specific dates.

Let us start with learning the syntax of Datedif(). This function generally does not appear in the DropDown list while writing, however we can directly start putting the function and it will work.

Syntax :- =Datedif(Start_Date, End_Date, Unit)

          Here, unit stand for type of information required. Values that can be used for unit is as below:

             

Unit	Returns
"Y"	The number of complete years in the period.
"M"	The number of complete months in the period.
"D"	The number of days in the period.
"MD"	The difference between the days in start_date and end_date. The months and years of the dates are ignored.
"YM"	The difference between the months in start_date and end_date. The days and years of the dates are ignored
"YD"	The difference between the days of start_date and end_date. The years of the dates are ignored.

Now in our Excel Sheet we can write the start date in a cell and similarly end date in another cell. Also, in a separate column, we can create a list of all the dates that need to be excluded. For example in our scenario, dates from 1/Apr/2015 to 30/Apr/2015 can be written in a column.

Refer below given picture:

Now since start date is in B2 cell and end date in B3 and difference is needed in terms of months, thus the formula will be:

=Datediff(B2,B3,"M")

Now to put the condition of not considering the dates in month of April, i.e. dates given in column D, we can combine Sumproduct with Datedif. Thus, the formula will be:

=DATEDIF(B2,B3,"M")-(SUMPRODUCT((D2:D31>=B2)*(D2:D31<=B3)))

However, since the difference is in terms of months, we need to divide SumProduct with 30. Thus, the final formula will be:

=DATEDIF(B2,B3,"M")-(SUMPRODUCT((D2:D31>=B2)*(D2:D31<=B3)))/30

Now had this been in terms of Years, we would have used "Y" instead of "M" in Datedif and divide Sumproduct with 365 instead of 30.

Similarly, in case of Days, we would have used "D" in Datedif and no division needed in Sumproduct.