Copy formula across 28 day dynamic shift calendar

W

Wolfgang17

Board Regular
Joined
Nov 8, 2010
Messages
63
How can I get the formula to start on the 2nd Monday in the 28 day rotation instead of defaulting to the first Monday, as is the case for April and other months. Is there another function I should use to find first empty cell from row above. if so how would I modify the original formula?

Shift Rotation-Test.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1202412 HOUR SHIFT SCHEDULE
2
3MTWTFSSMTWTFSSMTWTFSSMTWTFSS
40600Crew 432143214321
51800Crew 21P432143P2143
6OFFCrew 32A143214A3214
7ON CALLCrew 14Y321432Y1432
8January 202412345678910111213141516171819202122232425262728
9293031                         
10February 2024   12345678910111213141516171819202122232425
1126272829                        
12March 2024    123456789101112131415161718192021222324
1325262728293031                     
14April 202412345678910111213141516171819202122232425262728
152930                          
Shift Rotation
Cell Formulas
RangeFormula
A4A4="0600"
A8A8=DATE($A$1,1,1)
B8:AC15B8=IF($A8="",IF(MONTH($AC7+COLUMNS($B8:B8))=MONTH($AC7),$AC7+COLUMNS($B8:B8),""),IF(WEEKDAY($A8,2)=COLUMNS($B8:B8),$A8,IF(COLUMNS($B8:B8)=1,"",IF(A8<>"",A8+1,""))))
A10,A14,A12A10=DATE(YEAR(A8),MONTH(A8)+1,1)
Press CTRL+SHIFT+ENTER to enter array formulas.


Any help would be appreciated.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
Hi Wolfgang

Please try this

Weekdays.xlsx
I
81
Sheet1
Cell Formulas
RangeFormula
I8I8=IF(H8<>"",H8+1,IF(H7=DAY(EOMONTH($A6,0)),1,""))




Additionally, you may use a10=edate(a8,1); a12=edate(a10,1), a14=edate(a12,1) etc, which would index the months 1 month forward from the previous one
 
Upvote 0
The entire range had not been captured earleir
Please check this

Weekdays.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1MTWTFSSMTWTFSSMTWTFSSMTWTFSS
2Jan-2412345678910111213141516171819202122232425262728
3293031                         
4Feb-24   12345678910111213141516171819202122232425
526272829                        
6Mar-24    123456789101112131415161718192021222324
725262728293031                     
8Apr-24       123456789101112131415161718192021
9222324252627282930                   
10May-24         12345678910111213141516171819
11202122232425262728293031                
12Jun-24            12345678910111213141516
131718192021222324252627282930              
14Jul-24              1234567891011121314
151516171819202122232425262728293031           
16Aug-24                 1234567891011
171213141516171819202122232425262728293031        
18Sep-24                    12345678
199101112131415161718192021222324252627282930      
20Oct-24                      123456
2178910111213141516171819202122232425262728293031   
22Nov-24                         123
23456789101112131415161718192021222324252627282930 
24Dec-24                           1
25234567891011121314151617181920212223242526272829
Sheet1
Cell Formulas
RangeFormula
B2B2=IF(WEEKDAY($A2)=2,1,"")
C2,J2,Q2,X2C2=IF(B2="",IF(WEEKDAY($A2)=3,1,""),B2+1)
D2,K2,R2,Y2D2=IF(C2="",IF(WEEKDAY($A2)=4,1,""),C2+1)
E2,L2,S2,Z2E2=IF(D2="",IF(WEEKDAY($A2)=5,1,""),D2+1)
F2,M2,T2,AA2F2=IF(E2="",IF(WEEKDAY($A2)=6,1,""),E2+1)
G2,N2,U2,AB2G2=IF(F2="",IF(WEEKDAY($A2)=7,1,""),F2+1)
H2,O2,V2,AC2H2=IF(G2="",IF(WEEKDAY($A2)=1,1,""),G2+1)
I2,P2,W2I2=IF(H2="",IF(WEEKDAY($A2)=2,1,""),H2+1)
B3,B5,B7,B9,B11,B13,B15,B17,B19,B21,B23,B25B3=IF(DAY(EOMONTH($A2,0))>=AC2+1,AC2+1,"")
C3:AC3,C5:AC5,C7:AC7,C9:AC9,C11:AC11,C13:AC13,C15:AC15,C17:AC17,C19:AC19,C21:AC21,C23:AC23,C25:AC25C3=IF(B3<>"",IF(DAY(EOMONTH($A2,0))>=B3+1,B3+1,""),"")
B4,B6,B8,B10,B12,B14,B16,B18,B20,B22,B24B4=IF(AC2=DAY(EOMONTH($A2,0)),1,"")
C4:AC4,C6:AC6,C8:AC8,C10:AC10,C12:AC12,C14:AC14,C16:AC16,C18:AC18,C20:AC20,C22:AC22,C24:AC24C4=IF(B4<>"",B4+1,IF(B3=DAY(EOMONTH($A2,0)),1,""))
A4,A24,A22,A20,A18,A16,A14,A12,A10,A8,A6A4=EDATE(A2,1)
 
Upvote 1
Thank You, that worked nicely! However this is a dynamic calendar, how can I get excel to add another row automatically, if required as is the case for December 2024? This happens to March in 2025 as another example.
Once again thank you for all your help.
 
Upvote 0
I just added a 3rd row to fix the issue. However, I still have a problem when I change the year I get a value error in some months. I tried to figure out what is going on without any success.

202512 HOUR SHIFT SCHEDULE
MTWTFSSMTWTFSSMTWTFSSMTWTFSS
0600Crew 432143214321
1800Crew 21P432143P2143
OFFCrew 32A143214A3214
ON CALLCrew 14Y321432Y1432
January-251234567891011121314151617181920212223242526
2728293031
February-251234567891011121314151617181920212223
2425262728
March-251234567891011121314151617181920212223
2425262728293031
April-251234567891011121314151617181920
21222324252627282930
May-25123456789101112131415161718
19202122232425262728293031
June-25123456789101112131415
161718192021222324252627282930
July-2512345678910111213
141516171819202122232425262728293031
August-2512345678910
111213141516171819202122232425262728293031
September-251234567
89101112131415161718192021222324252627282930
October-2512345
678910111213141516171819202122232425262728293031
November-2512
3456789101112131415161718192021222324252627282930
December-25
#VALUE!#VALUE!##############################################################################
#VALUE!#VALUE!##############################################################################
MTWTFSSMTWTFSSMTWTFSSMTWTFSS
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
filling cells with using values in helper cell
Replies
0
Views
214
jbrown021286
J
C
  • Question Question
if Function
Replies
3
Views
281
ChetanPuri
C
SaraWitch
  • Solved
Return first Monday in April from one date and first Monday in current year April if another is blank
2 3
Replies
28
Views
2K
Dave Patton
D
K
  • Question Question
Trying to make a formula to figure out what total hours in the month will be, based on what I already know
Replies
1
Views
50
Sufiyan97
Sufiyan97
Sandeep Warrier
  • Question Question
Calculate No. of Working Days based on Shift Pattern
Replies
2
Views
149
Tetra201
Tetra201

Forum statistics

Threads
1,224,813
Messages
6,181,111
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top