Need to back out and distribute dollars across business quarters

[video_chuck]

Hi there,
I've been tasked with a problem in generating dollars amounts per business quarter.

Q1 = Apri1 1, 2014 - June 30, 2014
Q2 = July 1, 2014 - Sept 30, 2014
Q3 = Oct 1, 2014 - Dec 31, 2014
Q4 = Jan 1, 2015 - March 31, 2015

We have 5 employees that have started and finished in various quarters and many will run from, say, Mid Q2 to Mid Q4.

Excel has a function called NETWORKDAYS which does a calculation for the specific workdays and I know that, for this example, each employee is paid $100.00 per day.

I need to create an Excel work sheet which displays

Employee Name, Q1 dollar amount paid, Q2 dollar amount paid, Q3 dollar amount paid, Q4 dollar amount paid,

I'm having a tough time trying to break this down into pieces and I think that I have all the correct criteria.

Any help would be great.

Thanks
Video_Chuck

[LordLoki]

Hi chuck,

Can you provide a workbook with some example Data?
We need to know what your source Data looks like in order to help you with generating the output you want

Greets
Loki

[video_chuck]

Here it is ... let me know if it makes sense to you.

[LordLoki]

Here is a solution with a custom Function.
Actually its 2 Custom Functions cause the networkdays function from excel could not be used in VBA.

First the code for the Networkdays. This Code is not from me grabbed it once from somewhere cause its nice that you can add holidays and define the workingdays.

Please Login or Register  to view this content.

Next the Function that splits the Dates its very simple logic.
First 2 Parameters are the start and end of the Quartal.
next 2 Parameters are start and end of the Employee.
When the employee start is bigger then the Qx start his entry Day is the Startdate else its the first day of the Quartal
Same for the End Dates.
Attached the File with the implemented Formula

Please Login or Register  to view this content.

[video_chuck]

I believe this can be marked as Resolved. Loki's solution seems to have filled the bill. Thanks for the Help.