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.

Using Offset function for Calculation

Scenario: Given is the monthly data of a firm, we need to find out YTD and make it dynamic so that whichever month is selected by user, YTD of that month should appear. Which means data of all the previous months, including the selected month should be added up.

Solution : To create this we are going to use Data Validations for enabling user to select the month; Match function for finding column number which contains selected month. Sum for adding up the data and more importantly Offset function.

Syntax for Offset: =Offset(reference,row,cols,[height],[width])

Explanation :  

Parameter	Description
Reference	Starting cells
Rows	Number of rows to be moved from starting point
Cols	Number of columns to be moved from starting point
[Height]	Optional Parameter; Total number of rows to be included from start point
[Width]	Optional Parameter; Total number of columns to be included from start point

Data Example:

The Data Sheet

Considering the data shown above, Let say we want to calculate Total sales up till the selected month.In cell O1 Data Validation is used so as to enable user to select the month.

Now to find how many months are selected we can use Match function in cell P1. In Match, we can select the O1 cell as Lookup value, the headers in data as the Array and 0 for exact match. And so, the formula will be:

=MATCH(O1,C2:N2,0)

So if June is selected, answer of Match function will be 6, as June is written in the 6th cell of the selected range in Match.

Now to calculate YTD, we can use Offset. In Offset, our starting cell is the first cell of Jan data i.e. C3. This is because that is from where we will start adding. In this case we do not have to move any number of rows or columns. Our starting point is always same thus we can put 0 (Zero) for rows and Cols parameter.

Similarly the height is also Zero as for one sales person, we have to consider data written in one row. Thus, the data does not stretch Row- Wise. 

However, the Width is needed as we need to include multiple cells in one row at a time. So if Jun is selected, we would need total of 6 cells (From Jan to Jun) in a single row but multiple columns. This height we have pre-Calculated using Match function in cell P1.

And so our Offset Function Should be : =OFFSET(C3,0,0,1,$P$1)

Now whichever range is selected by Offset needs to be totalled up in order to calculate YTD. Thus, we can combine Offset with Sum function.     =SUM(OFFSET(C3,0,0,1,$P$1))

Image for reference

Now if we drag the function, we will get YTD for all the Sales People and making it dynamic for whichever month is selected by user.

Download the Excel data file for reference from below given link

https://drive.google.com/file/d/0By0JZ7H_KuKjYlhweVZhUm5UYmM/view?usp=sharing