What exactly is the payroll period?
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?
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.
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_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…
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.
