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 automatically after entering a few variables etc. Now my question is this,
I am trying to come up with a formula for the days my pay goes into my bank which it the 10th of each month but if the 10th lands on a Saturday or Sunday it will go in on the Friday before e.g. if the 10th is a Saturday it will go in on the 9th etc.
Also is there a way for my to copy the current sheet into a new one I know you can copy the sheet into a new one but is there some sort of logic that I can put into the document that if I create a copy and change the year that it looks at the previous year and continues on with the pattern of my shifts?
You can find the document here to see what I mean it's a template that I found online and removed a few things that I don't need.
eoghan61 As a variant to DexterG_III 's solution, I chose another route.
First of all, the way this schedule is set up, the 10th day of the month can only occur in columns K:Q. So there is no need to fill the entire Pay Days row with formulae.
Secondly, I though it would be easier to calculate a list of pay dates like in the picture below:
The formula takes the 10th in any month and deducts the result of checking if the WEEKDAY number is 7 (Saturday) or 1 (Sunday), returning either 1 or 2, otherwise 0.
Now you can simply test if the dates on rows 12, 16, 20 etc. in columns K:Q are found in the list of PayDays (a named range).
=IF(ISNUMBER(MATCH(M12,PayDays,0)),"p","")See attached.
- Oh now that does the trick thanks and DexterG_III thanks also for the tips. My next question is with the schedule in the holidays sheet is it possible to make it somewhat smart so that if I create a rota for say next year could it automatically adjust based on the sheets name?
All i need to fix now is getting my shift patterns to line up correct they seen to be running off pattern in some areas so some reasoneoghan61 Don't understand the question, sorry. What do you mean by "making holidays somewhat smart"? Furthermore, it's probably a bad idea to want to create something that automatically rolls forward into another sheet. I believe the schedule is already complicated as it is right now.
But perhaps you can just add months below the current months and hide the ones that have passed. E.g. copy rows 12:14 and paste them to rows 60:62 and update the formula in A60 so that it returns January 2023.
And with regard to the shifts going off pattern, I can't really tell where that happens.
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.
