CODEWITHSUNDEEP
exceldateexcel-formula
tester42
tester42

Reputation: 21

Determine number of not overlapping days of some data ranges

I have got a table consisting of a DateIn and a DataOut value and a ProductName:

DateIn         DataOut          RroductName
14.01.2015     25.02.2015       Table
23.03.2015     02.05.2015       Chair
09.02.2015     30.03.2015       Table
01.05.2015     23.06.2015       Table 
...            ...              ...

Now I need to calculate the number of not overlapping days of all data ranges of a specific product.

That means for example for the product Table the output should be 31 because between the 14th January and 30th March there was a table in the stock but between the 1st and 30th April there was no chair available.

I need this information for many products, but I can´t figure out how to do this in excel. Do you have any ideas? Thanks in advance.

Upvotes: 2

Views: 374

Answers (1)

bbishopca
bbishopca

Reputation: 296

You probably should look into SUMPRODUCT. First you need to format your dates as a date for easier calculation. Then try this sample equation that needs to be modified for your purpose.

=SUMPRODUCT(($D$4:$D$7="Chair")*1, ($C$4:$C$7)-($B$4:$B$7))

So this looks in the D column for the item you want and if it matches then it will subtract the DateIn from the DateOut to give a total number of days it was checked in.

This retuns 40 for chair and 144 for table. The only thing that needs to be added is a way to not count duplicate times. Maybe someone else can weight in on that step or if I think of a good way I will update my answer.

Upvotes: 1

Related Questions