Converting Date Range to Date Series

Dear All

With reference to the attached spreadsheet and screenshot below I have a situation where the user enters employee holidays as a start date and end date (black table called data_tbl). In addition the user specifies if the holiday starts and ends in the morning or afternoon to allow for a 1/2 day at the either end of the holiday period. The number of days taken is calculated using the Networkdays function, in combination with IF functions, to determine the number of days taken.

Capture.JPG


As further processing of the data is required by way of using pivot tables / charts I need to convert the data entered by the user in to a new table similar to the blue table depicted called list_tbl. The new table should indicate if a 1/2 or whole day was taken for each date and allowance made so the it will refresh when data is amended, added or removed from the data_tbl. I guess this is a job for vba. I would welcome any assistance as my knowledge of vba is limited.

Thank you CK76. Sorry i should have mentioned the solution needs to work in 2010

2010 can have PowerQuery add-in and is free. Since it doesn't use data model. PQ functions work in both versions. Just return the result to sheet instead of loading to model.

But I get that it may not be an option to install add-in. I'm bit busy right now, but will look at coding later, if others hasn't helped you by then.

Simply

Please Login or Register  to view this content.

Thank you both very much, CK76 and jindon. Now i see the vba I can understand what it is doing but i would not have got there on my own in a million years. Will study and try to learn.

You are welcome and thanks for the rep.

However, I had misread you problem... should be like this.

Please Login or Register  to view this content.