Forum Discussion
Worksheet and import data
Hi all new to the forums and excel in general I am looking for a bit of help/advice I am using excel to create a spreadsheet with all the months of the year with my shifts in work planned out auto...
eoghan61 hello eoghan,
I think I understand what you are looking for. All paydays will either be on the 10th, or the Friday preceding the 10th (if the 10th is on the weekend). Please refer to the Pay Days Alt rows in the screenshot below. To achieve this I used the formula (entered in B15 and copied to all cells in the pay day alt row for each month):
=IF(B12="","",IF(AND(DAY(B12)=10,WEEKDAY(B12,16)>2),"P",IF(AND(DAY(B12)>7,DAY(B12)<10,WEEKDAY(B12,16)=7),"P","")))
To continue your pattern you must make three changes on the new worksheet:
1) Change the year
1) Change the value in P6 to be a formula =DATE(A4,1,1)
2) Change the value in P5 to be a formula:
=MID('2021'!P5,A4-'2021'!A4+1,LEN('2021'!P5)-(A4-'2021'!A4))&LEFT('2021'!P5,A4-'2021'!A4)
This formula shifts your pattern to the left by one (moves the first character to the last position) for each year after it started.