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

  • 0
  • 1
  • Question
  • Updated 3 months ago
  • Answered

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?

Photo of Matthew

Matthew

  • 60 Points

Posted 3 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,448 Points 50k badge 2x thumb
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
Photo of Matthew

Matthew

  • 60 Points
The formula returns a "1" for each week
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,448 Points 50k badge 2x thumb
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
Photo of Matthew

Matthew

  • 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!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,448 Points 50k badge 2x thumb
Great, thx for letting me know.
Photo of Carol Short

Carol Short

  • 184 Points 100 badge 2x thumb
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.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,448 Points 50k badge 2x thumb
I suggest that you post this as a new question.
Photo of Carol Short

Carol Short

  • 184 Points 100 badge 2x thumb
Will do, thank you

This conversation is no longer open for comments or replies.