working days for start and end date

[rrocker1405]

Hi,


I'm trying to populate working days between start date and end date. For example, column A has start date as 07/06/2012 and column B has end date as 25/08/2012. I should get my start month as june and the number of working days from 7th june to end of june and the following columns should project the number of working days untill 25th august only. so far i have succeeded in the first part and not sure how to stop the calculation for the end date. Attached is a sample sheet for easier reference.


can there by a way to solve it?


regards....A!

 

[Hui]

A


Firstly, Welcome to the Chandoo.org forums.


There is nothing attached?


Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook

for instructions on posting files

 

[rrocker1405]

Sorry forgot to include the link of the file. Here is the link for your refrence.


https://docs.google.com/spreadsheet/ccc?key=0AgqXxSvYwRm8dHRJRzJ6VVRqbEtYdkNMYkhrdk91THc


regards...A!

 

[Hui]

A


You may want to have a look at the Techniques described here: http://chandoo.org/wp/2012/05/03/formula-forensic-020/

 

[Hui]

To save you some time

1. Change the dates in H1:S1 to be the 1st of each month 4/1/2012, 5/1/2012, 6/1/2012 etc

2. In H6:

=MAX(0,NETWORKDAYS(MAX($B6,H$1),MIN($C6,EOMONTH(H$1,0))))

copy across

3. Enjoy

[pre]April	May	June	July	August	September	October
0	0	17	22	18	0	0

[/pre]

 

[rrocker1405]

that worked, like magic. Thank you so much


Regards....A!

 

[Hui]

A.


It is a very impressive and powerful formula

Well worth the trouble learning