## Monday, January 27, 2014

### yogi_Set Up FORMULAS for WEEKDAYS between Starting Period Date and Ending Period Date

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-27-2014
How to make Work days auto propagate in a mothly calendar
So this is my first post in this forum so i hope im in the right place.

I need help making days auto propagate based on a real time month calendar year. For example if i want to make a 2-3 month span of Jun-Jul 2013 for example this is what i want:

 JUN 2013 JUL 2013 WEEK 1 WEEK 2 WEEK 3 WEEK 4 WEEK 1 WEEK 2 WEEK 3 WEEK 4 WEEK 5 3 4 5 6 7 10 11 12 13 14 17 18 19 20 21 24 25 26 27 28 1 2 3 4 5 8 9 10 11 12 15 16 17 18 19 22 23 24 25 26 29 30 31

thats all manually typed in but i want to use a function to type in the first day and have the rest fill in.

at first i used this but then just got the days with the next one below
=ArrayFormula( text( date( year(A6) , month(A6) , day(A8) + column(A1:AQ1) ),"m/d/yyyy") )

this one i used to put the first day of that month and it auto fills in the range i set.
=ArrayFormula( text( date( year(A6) , month(A6) , day(A9) + column(B9:AQ9) ),"d") )
I fill in 3 then in 4's spot i put this code and it fills from 4 on to end range, but it does every day of the month (including weekend dates)
3 4 5 6 7 8 9 10 etc..

this formula does NETWORKDAYS which is work days mon-fri

=IF(WEEKDAY(A25)=7,A25+2,IF(WEEKDAY(A25)=6,A25+3,A25+1))

but this requires it to have a full date (6/20/2013) in the starting cell to the left of it then click and drag to all the cells you want to the right
also i dont know how to just get the day # instead of the full date m/d/yyyy

Is there a way to mix the 2 of these formulas?
The first formula is the one i want to use because it auto fills days to the right of it without having to click/drag and it also just outputs the day value # but i need it to only output WEEKDAYS workdays mon-fri
Is there a way to make that formula work for what i need?

Thanks for the help :)

-------------------------------------------------------------------------------------------------------------------------------------------------