# How to identify each week for a pay period as week 1 and week 2?

• 0
• Question
• Updated 6 months ago

I have a time sheet application that I built that collects hours on a weekly basis for each employee. Our work week starts on Sunday and ends Saturday, but the pay period is bi-weekly, so there's two weeks for each period that go into our payroll system. Everything exports out of QBase perfectly except for the [BatchID] field, which needs to identify which week it is. Our weeks are identified by a 1 or 2.

What I need is a formula that will look at a date field [Week Ending] and output either a "1" or "2" based on which week of the period we're in. We just implemented this so, if it helps, I can use the date 2-27-2016 as a starting point, as there will never be a time card date prior to that. Can anyone help?

• 60 Points

Posted 3 years ago

• 0

QuickBaseCoach App Dev./Training, Champion

• 67,620 Points
Not tested but try this

IF(LastDayOfPeriod([Week Ending], Weeks(2), Date(2016,2,27)) = LastDayofWeek([Week Ending]),2,1)

The magic is in this formula function here

https://www.quickbase.com/db/6ewwzuuj?a=dr&rid=42&rl=j9
• 60 Points
The formula returns a "1" for each week

QuickBaseCoach App Dev./Training, Champion

• 67,620 Points
Hmm, that seems unlikely. Can you give me an example of two records you are working with, which should have different results?  ie the dates of two records?
Mark
• 60 Points
Fixed. The reference date should be the starting date of the period, which would be the 28th, not the 27th.

If(LastDayOfPeriod([Week Ending], Weeks(2), Date(2016,2,28)) = LastDayOfWeek([Week Ending]),2,1)

Thank you!

QuickBaseCoach App Dev./Training, Champion

• 67,620 Points
Great, thx for letting me know.
• 184 Points
I have a question regarding week #s although slightly different issue.  We represent a German machine manufacturer.  They use ISO week # (Week 1 thru Week 52, first day of the week is always Monday).  I don't even know where to start.  I thought about creating a table, but it would change every year.

QuickBaseCoach App Dev./Training, Champion

• 67,620 Points
I suggest that you post this as a new question.
• 184 Points
Will do, thank you

This conversation is no longer open for comments or replies.