Discussions

Expand all | Collapse all

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

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

    Posted 03-09-2016 16:02

    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?



  • 2.  RE: How to identify each week for a pay period as week 1 and week 2?

    Posted 03-09-2016 18:36
    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


  • 3.  RE: How to identify each week for a pay period as week 1 and week 2?

    Posted 03-09-2016 19:24
    The formula returns a "1" for each week


  • 4.  RE: How to identify each week for a pay period as week 1 and week 2?

    Posted 03-09-2016 19:27
    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


  • 5.  RE: How to identify each week for a pay period as week 1 and week 2?

    Posted 03-09-2016 19:30
    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!


  • 6.  RE: How to identify each week for a pay period as week 1 and week 2?

    Posted 03-09-2016 20:05
    Great, thx for letting me know.


  • 7.  RE: How to identify each week for a pay period as week 1 and week 2?

    Posted 12-05-2018 20:19
    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.


  • 8.  RE: How to identify each week for a pay period as week 1 and week 2?

    Posted 12-05-2018 20:56
    I suggest that you post this as a new question.


  • 9.  RE: How to identify each week for a pay period as week 1 and week 2?

    Posted 12-05-2018 20:57
    Will do, thank you