Monday, January 27, 2014

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

                                         Google Spreadsheet   Post  #1502
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-27-2014
post by NetEngineer009 (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/KahpovREU1s)
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 2013JUL 2013
WEEK 1WEEK 2WEEK 3WEEK 4WEEK 1WEEK 2WEEK 3WEEK 4WEEK 5
34567101112131417181920212425262728123458910111215161718192223242526293031

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 :)

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


No comments:

Post a Comment