fill a row with weeks of a particular month

roninn75

New member
Joined
Jan 17, 2013
Messages
11
Reaction score
0
Points
0
hi

in my sheet i have a cell that displays the month. below that i have the amount of days for that particular month. the following row holds standard values (a to c which depicts shifts) that is coloured. each shift is on for 7 days.
i want to the shifts to 'move' accordingly if the month is changed to the next month.
so looking at the sample workbook at the end of January shift B will only have worked 4 of their 7 days required, therefor February 1 to 3 will display shift B. then February 4 to 10 will display shift C, and so on...

any help is greatly appreciated.
 

Attachments

  • Book2.xlsx
    8.6 KB · Views: 24

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
I think this will look after what you are trying to do. Just run the macro and put a date in the input box.

Good Luck
NoS
 

Attachments

  • Copy of Book2.xlsm
    18.8 KB · Views: 25

roninn75

New member
Joined
Jan 17, 2013
Messages
11
Reaction score
0
Points
0
thank you NoS, this seems to work... i will run through it and come back to you if i have any questions if that is ok...
 

roninn75

New member
Joined
Jan 17, 2013
Messages
11
Reaction score
0
Points
0
Hi NoS, should i wish to display this over a period of 3 months (so i can have a form with 4 radio buttons that can do the selection) each radiobutton once clicked will display 3 months. so radiobutton 1 = jan - march, radiobutton 2 = apr - jun, etc. that way no need to type a period as it is set... could you please assist?
 

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
Hello roninn75

I don't know where you are going with this. It would appear that something with a weekly schedule like this would have the section below the date used for check marks or something.

So I guess the question is if you're looking at command buttons that show 3 month sections of a much larger table?

NoS
 

roninn75

New member
Joined
Jan 17, 2013
Messages
11
Reaction score
0
Points
0
NoS, basically this is part of a larger table yes. however the selection for this particular part is stand alone. meaning that a userform will be used that if the radiobutton or normal button is selected, it will display the schedule like the above workbook does. the rest of the tables are derived from that with dropboxes and vlookups... that part i have sorted already. initially it was meant to be a monthly period but that will be unnecessary if i can do it quarterly. i hope that makes sense.
 

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
Roninn75

Here's a workbook with macro that will give shift schedule for what ever dates you manully enter into the modules code, from 1 day up to however many columns Excel has.
Added code to limit to 1 year because 10 years went a long way across the page.

Be aware this VBA is not sheet specific. It does its thing on the active sheet.

Hope this helps towards achieving what you're after.

NoS
 

Attachments

  • Annual_Quarterly_shiftweeks.xlsm
    30.1 KB · Views: 24
Top