networkdays by quarter?

[Ap1_Alan]

Hi everyone,

i have a list of start & end dates, i need to calculate networkdays by quarter

ex: (days used for example only, i might be off)

start_date end_date Q1_workdays Q2_workdays
1/1/2011 4/30/2011 65 22


Any suggestions?

 

[barry houdini]

Assuming you have start date in A2 and end date in B2 then list the start date of each quarter in C1 across, e.g. in C1 1-Jan-2011, in D1, 1-Apr-2011, in E1 1-Jul-2011 etc. then you can use this formula in C2 copied across (and down if required) to give number of days per quarter for each date range

=MAX(0,NETWORKDAYS(MAX($A2,C$1),MIN($B2,EOMONTH(C$1,2))))

I get 64 and 21 for your example......

 

[Ap1_Alan]

worked perfectly!


Thanks!