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

Question

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?

quickbasecoachd · Answer

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

matthewmatthew1 · Answer

The formula returns a "1" for each week

quickbasecoachd · Answer

Hmm, that seems unlikely. Can you give me an example of two records you are working with, which should have different results?&nbsp;&nbsp;ie the dates of two records?Mark

matthewmatthew1 · Answer

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!

quickbasecoachd · Answer

Great, thx for letting me know.

Forum Discussion

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

Related Content

Loan database - Interest period

Reporting to identify blank or missing data

Identifying identical values between two table

Conversion from user identifier to UserList

Creating a Unique Identifier