Skip to main content

Payroll: How to show only dates within rolling fortnightly periods

T

Hi all

I have a base for managing casual employees’ hours worked. Employees submit hours via a form and payroll uses the entries to pay wages.

Payroll would like to see only the records for the relevant payroll period: 16 May - 19 May and every two week period thereafter.

Is there any way to filter by these rolling fortnightly periods?

Thanks!
Travis

Further notes:

I found that I could get fortnightly periods using the week numbers: ROUND(WEEKNUM(Date)/2)

The trouble is that the payroll period is not exactly in sync with the week numbers… Maybe there could be a way to shift the period?

    6 replies

    W
    Forum|alt.badge.img+5
    • W_Vann_Hall
    • Inspiring
    • 1386 replies
    • May 27, 2018

    What exactly is the payroll period?

    T
    W_Vann_Hall wrote:

    What exactly is the payroll period?


    Hi

    The current payroll period is Wednesday 16 May to Tuesday 29 May 2018.

    The payroll period is every two week period thereafter: Wednesday 30 May to Tuesday 12 June; Wednesday 13 June to Tuesday 26 June 2018 etc.

    Does that make more sense?

    W
    Forum|alt.badge.img+5
    • W_Vann_Hall
    • Inspiring
    • 1386 replies
    • May 28, 2018
    Travis_Monczko wrote:

    Hi

    The current payroll period is Wednesday 16 May to Tuesday 29 May 2018.

    The payroll period is every two week period thereafter: Wednesday 30 May to Tuesday 12 June; Wednesday 13 June to Tuesday 26 June 2018 etc.

    Does that make more sense?


    Try using the following to generate your fortnight number:

    EVEN((VALUE(DATETIME_FORMAT(DATEADD(Date,2,'d'),'X'))/86400)/7)

    That is based on Unix time, and it has the added bonus of uniquely identifying each two-week period from January 1, 1970 through early January 2038 — in other words, it will span year-ends without needing to be tweaked. The DATEADD() function brings the period into a Wednesday-to-Tuesday span synchronized with 16 May 2018. It also assumes all dates are kept in UTC, which may not be possible for everyone; depending on your application, you may need to adjust that offset +/- a day.

    To display the current pay cycle, you can use this formula:

    IF(
    Fortnight,
    DATETIME_FORMAT(
        DATETIME_PARSE(
            ((Fortnight-2)*86400*7)-86400,
            'X'
            ),
        'MM-DD-YYYY'
        )&' – '&
        DATETIME_FORMAT(
            DATEADD(
                DATETIME_PARSE(
                    ((Fortnight-2)*86400*7),
                    'X'
                    ),
                12,
                'd'
                ),
            'MM-DD-YYYY'
            )
    )

    That will return (for instance)

    05-16-2018 – 05-29-2018

    If you need to tweak it, you can do so by either adding or subtracting increments of 86400 (number of seconds in a day), as shown in the ‘start date’ calculation, or by modifying the DATEADD() amount, as shown in the ‘end date’ calculation.

    To change how the date is formatted, change the format specifier in the DATETIME_FORMAT() function.

    T
    O
    J
    3 people like this
    • T
      Travis_Monczko
    • O
      Omar_Ramos
    • J
      Jeremy_Oglesby
    T
    W_Vann_Hall wrote:

    Try using the following to generate your fortnight number:

    EVEN((VALUE(DATETIME_FORMAT(DATEADD(Date,2,'d'),'X'))/86400)/7)

    That is based on Unix time, and it has the added bonus of uniquely identifying each two-week period from January 1, 1970 through early January 2038 — in other words, it will span year-ends without needing to be tweaked. The DATEADD() function brings the period into a Wednesday-to-Tuesday span synchronized with 16 May 2018. It also assumes all dates are kept in UTC, which may not be possible for everyone; depending on your application, you may need to adjust that offset +/- a day.

    To display the current pay cycle, you can use this formula:

    IF(
    Fortnight,
    DATETIME_FORMAT(
        DATETIME_PARSE(
            ((Fortnight-2)*86400*7)-86400,
            'X'
            ),
        'MM-DD-YYYY'
        )&' – '&
        DATETIME_FORMAT(
            DATEADD(
                DATETIME_PARSE(
                    ((Fortnight-2)*86400*7),
                    'X'
                    ),
                12,
                'd'
                ),
            'MM-DD-YYYY'
            )
    )

    That will return (for instance)

    05-16-2018 – 05-29-2018

    If you need to tweak it, you can do so by either adding or subtracting increments of 86400 (number of seconds in a day), as shown in the ‘start date’ calculation, or by modifying the DATEADD() amount, as shown in the ‘end date’ calculation.

    To change how the date is formatted, change the format specifier in the DATETIME_FORMAT() function.


    @W_Vann_Hall, your solution worked beautifully! Thank you.

    I was missing that fixed date as a signpost that could be used to calculate other date periods. Good idea to use Unix time.

    W
    Forum|alt.badge.img+5
    • W_Vann_Hall
    • Inspiring
    • 1386 replies
    • May 28, 2018
    Travis_Monczko wrote:

    @W_Vann_Hall, your solution worked beautifully! Thank you.

    I was missing that fixed date as a signpost that could be used to calculate other date periods. Good idea to use Unix time.


    Yeah, I’ve started using Unix time more often because it’s so much easier than having to code around the December → January transition — especially given Airtable’s current lack of persistence. Otherwise, there’s too great a chance of this year’s data stepping on last year’s…

    M
    • Matt_Schmuff
    • New Participant
    • 1 reply
    • October 1, 2020
    W_Vann_Hall wrote:

    Try using the following to generate your fortnight number:

    EVEN((VALUE(DATETIME_FORMAT(DATEADD(Date,2,'d'),'X'))/86400)/7)

    That is based on Unix time, and it has the added bonus of uniquely identifying each two-week period from January 1, 1970 through early January 2038 — in other words, it will span year-ends without needing to be tweaked. The DATEADD() function brings the period into a Wednesday-to-Tuesday span synchronized with 16 May 2018. It also assumes all dates are kept in UTC, which may not be possible for everyone; depending on your application, you may need to adjust that offset +/- a day.

    To display the current pay cycle, you can use this formula:

    IF(
    Fortnight,
    DATETIME_FORMAT(
        DATETIME_PARSE(
            ((Fortnight-2)*86400*7)-86400,
            'X'
            ),
        'MM-DD-YYYY'
        )&' – '&
        DATETIME_FORMAT(
            DATEADD(
                DATETIME_PARSE(
                    ((Fortnight-2)*86400*7),
                    'X'
                    ),
                12,
                'd'
                ),
            'MM-DD-YYYY'
            )
    )

    That will return (for instance)

    05-16-2018 – 05-29-2018

    If you need to tweak it, you can do so by either adding or subtracting increments of 86400 (number of seconds in a day), as shown in the ‘start date’ calculation, or by modifying the DATEADD() amount, as shown in the ‘end date’ calculation.

    To change how the date is formatted, change the format specifier in the DATETIME_FORMAT() function.


    This post is a couple years old but I’ll give commenting a shot.

    I can’t figure out how to correctly modify this to our payroll schedule which is fortnightly from Saturday to Friday (Our current payroll ends tomorrow on 10/2). This is the only formula of it’s kind that I’ve found online. Seems like this would be a more in demand request.

    Reply


    Most helpful members this week

    Terms & Conditions