Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Please Help. WeekNum to Pay Period column/Formula

l'm creating a workbook for tracking meetings. I've just generated a pivot table calendar from a sheet with the following columns: "Date, WeekNum, WeekDay, Day, Month." 

 

It's great! However, I desperately need either the week number to align with the pay period calendar (starting on XX/XX/YR instead of 01/01/YR), or I need a column that is going to show the pay period week formulated off of the week number- but starting over at the end of the pay period year (So it can't just be a basic cell/value+#). 

 

Also, I'm COMPLETELY new to this, so I'm way in over my head with the formulas- but work is work. So, basically explain step by step what to do, or else I'll be lost.

 

Thanks! 

formulas
Help
payperiod
WEEKNUM

1 Reply

  • DallasBaba's avatar
    DallasBaba
    Skilled Sharer
    2 years ago

    Anonymous 

    Start by adding a column in your data where you will input the starting date of your company's pay period.
    Let's call this column "PayPeriodStart."
    Next, you'll create a formula to calculate the pay period week based on the "Date" and "PayPeriodStart" columns.

     

    PayPeriodWeek : =INT((Date - PayPeriodStart + 1) / 7) + 1


    Enter the formula in the cell next to your data in the "Pay Period Week" column.
    Drag the fill handle (a small square in the bottom right corner of the selected cell) down to apply the formula to all rows.

     

    Your data should have a new column, "Pay Period Week," that calculates the week number based on your company's pay period start date.

     

    Let me know if this work