Calculate&Spread networking days over different intervals (Clause:check Vacation interval)

[sumans054]

Vacation Networking days Resource Sprint 7.1 Dates Sprint 7.2 Dates Sprint 7.3 Dates Total
Resource Start End 05/01/19 - 05/14/19 05/15/19 - 05/31/19 06/01/19 - 06/15/19
John 05/12/19 05/16/19 4 John 2 2 0 4
Pat 5/26/2019 06/05/19 8 Pat 0 5 3 8
Jerry 5/16/2019 5/16/2019 1 Jerry 0 1 0 1
Steve 06/10/19 06/17/19 6 Steve 0 0 5 5


"Vacation days which are on to the left; for example 4 Networking days which are in cell E4 should be split in between sprint 7.1 and sprint 7.2 and so on …. Right now whatever values you are seeing in this table are manual entires but I want these to be calculated somehow using a formula.

Please take a closer look at the Vacation Dates which are in table 1 vs sprint dates which are in table 2 these are highligted in Red "

For additional details, please take a look at attached workbook.

Any help is really appreciate and thanks a bunch in advance!!

[FlameRetired]

Welcome to the forum sumans054.

Try this in H4 filled down and across.

Please Login or Register  to view this content.

Does this do what you expect?

[FlameRetired]

Here's another one. A little shorter but resource hungry and volatile.

Please Login or Register  to view this content.

[sumans054]

This is working perfectly as expected. Thanks a ton Dave for the formula and getting me this so quickly!!

[sumans054]

=INDEX(FREQUENCY(NETWORKDAYS(ROW(INDIRECT($C4&":"&$D4)),ROW(INDIRECT($C4&":"&$D4)))*ROW(INDIRECT($C4&":"&$D4)),H$3:I$3-{1,0}),2)

Even this formula suffices my needs; thanks a bunch for the quick turnaround!!! Appreciate your help!!

[FlameRetired]

You are welcome. Thank you for the feedback.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[sumans054]

Hello Dave,

Thought of closing this but I got stuck with something. I want to segregate duplicates and was using Index and Match formulas (please see below) but not sure this isn't working for me. Other than this was searching in internet and used different other formula nothing is working at all. Can you please take a look at it.
=INDEX($G$22:$G$26, MATCH(0, COUNTIF($G$66:G66, $G$22:$G$26), 0))

Not just stopping at this I have one another requirement, can you please check my attachment to get more insight of it and my ask .... thanks a ton in advance!!

Regards,
Suman

[FlameRetired]

Suman,

I will look at this. In the meantime you might want to mark your thread UNsolved to attract more participants.

Additionally this post will "bump" your thread back to the top of the queue.

[sumans054]

Suman,

I will look at this. In the meantime you might want to mark your thread UNsolved to attract more participants.

Additionally this post will "bump" your thread back to the top of the queue.

Dear Sir,

First of all, thanks for saying you look into it!!

The thing is, it is resolved and so I request you to not to put any more efforts on this.

Just now I saw came to know that we can recognize people who helped us and that’s the reason I hit a star sign. Maybe because of which some alert might have came to you and you might thought this wasn’t resolved. Sorry if that is the case.

Regards
Suman

[FlameRetired]

Thank you for letting me know, and thank you for the added rep.