Consolidate Rows Based on Holiday Dates

Question

I'm relatively new to Excel and I am looking for a way to consolidate rows of multiple holidays for the same person into one row. This is what some sample data looks like;

I would like to end up for one row for this person, but with the NumOfDays added up, so in this example "Test Person" would have 7 in their NumOfDays column, but only one row. I can imagine that is not too hard, though I'm still not sure how to do it.

Where it gets complicated (for me at least) is that we are only interested in HolidayTo up to 30/09/2016. So for example, if someone has a HolidayFrom 28-09-2016 and a HolidayTo of 03-10-2016 then this would list it as 6 NumOfDays whereas I would only want to record 3 (28/29/30) up to the 30th September.

Is there a way I can do all this in Excel? (All dates are in UK format, dd/MM/yyyy)

Answer 1

Helper Column

To stop counting days on 30.09.2016 you could use the following helper column "Difference to 1.10.2016" and formula:

=IF([@HolidayTo]<DATE(2016;10;1);0;DATEDIF([@HolidayFrom];DATE(2016;10;1);"d"))

As well as a formula for your "NumOfDays" column:
=IF([@[Difference to 1.10.2016]]=0;DATEDIF([@HolidayFrom];[@HolidayTo];"d")+1;[@[Difference to 1.10.2016]])

This way you have in column "NumOfDays" only the number of days up to 01.10.2016. If later you want to change the end date, you can do so in the formula.

Pivot Table

There are several books, video tutorials and websites on pivot tables and how to create/use them. Since your question can be answered with a "simple" pivot table, you can start with Microsoft Support:

• Click any cell in the range of your cells with data
• Click Insert, then Pivot table
• Choose the fields (aka columns name and days)

It could look like this:
.