Networkdays Calculation Array

[Joecam]

Hello all,

I get a data dump in Excel daily that I need to convert into a usable report. I'm looking to streamline my work process. Basically in Column E I get a list of between 2500 & 3500 dates and I need to turn that into a table that counts and displays how many days old each of those dates are. To further complicate the task, any date that falls on a weekend & holiday needs to be rolled over to the next workday.

I'm at a total loss on how to approach this problem. Ideally I'd like a formula or group of formulas that I can just paste into my spreadsheet every day and it will spit out the report. In order to do that, the formula needs to recognize the date, count the number of times the date appears, and then do a calculation that counts how many days old while also rolling the weekend/holiday days to the next workday.

Anyone have any thoughts on how this might be accomplished?

 

[AliGW]

Attach a sample workbook that shows what you want (manually created).

 

[Joecam]

Thanks Ali!

This is the work that I started on this project last night, which has infinitely improved the process from an entirely manual one.

Column C I get all of the unique dates from Column A.
Column D counts the number of instances of each date.
Column E tells me if it is a workday or not (holiday list in column K).

Columns G & I are the final output that I am looking for though. If there is a formula that can turn column I into an automated event when I paste this into my daily spreadsheet, that's what I'm working towards.

Let me know what you think!

 

[Bob Phillips]

JUst add a helper column to calculate the work date, formula =WORKDAY(A2-1,1), then a pivot table on this new column.