Payroll: How to show only dates within rolling fortnightly periods

[Travis_Monczko]

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?

[W_Vann_Hall]

What exactly is the payroll period?

[Travis_Monczko]

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_Vann_Hall]

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.

[Travis_Monczko]

@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_Vann_Hall]

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…

[Matt_Schmuff]

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.