Forum Discussion

eoghan61's avatar
eoghan61
Copper Contributor
Sep 17, 2022

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.

Excel
Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Sep 17, 2022

    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.

     

    • eoghan61's avatar
      eoghan61
      Copper Contributor
      Sep 17, 2022
      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 reason
      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor
        Sep 17, 2022

        eoghan61 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.

         

  • DexterG_III's avatar
    DexterG_III
    Iron Contributor
    Sep 17, 2022

    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.