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:
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(WE EKDAY(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 :)
-------------------------------------------------------------------------------------------------------------------------------------------------
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 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(WE
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