Okay I believe I have finally solved this problem. Using a bunch of nested formulas and heaps of hints from these forums I managed to take the start date(/time) of an event in my table, and calculate the correct payroll date. If you've ever tried this before you may have come across several complications which generally come back to Airtable performing all calculations on a date field without considering the timezone that the displayed date field is in - it sometimes assumes date fields need to be translated back into GMT, and at others it just assumes they are already in GMT when they aren't... etc. Formulas like WEEKNUM() and SET_TIMEZONE() don't seem to work well together... etc.
Anyway, I have tested this formula with a bunch of tricky dates around the start/end of a year and the start/end of a pay period and haven't come across any errors in the calculations yet. So my fingers are crossed that this formula is the solution that works for you too. Or, you know, someone better than me will come along and point out some glaringly obvious shortcut that I missed entirely, or a mechanism by which my formula still spits out an incorrect result. We shall see.
For context, I pay my payroll for a fortnightly period, starting on a Monday and ending 14 days later on the Sunday. Actual payroll is processed 3 days after the end of the period, so the following Wednesday.
E.g. a job/shift happens on Monday, 27 Feb 2023, displayed as 27/2/2023 (note the format DD/MM/YYYY rather than American's more usual MM/DD/YYYY). That particular Monday is the start of a fortnightly pay period, so the last shift to be included in that pay period is two weeks later, or Sunday 12 March 2023. This entire pay period will be collated and paid out to the employees on the Wednesday immediately following - i.e. Wednesday 15 March 2023. The point of this formula is, of course, (1) to show employees what date they can expect to be paid for the job/shift they have agreed to do, and (2) to help payroll calculations by only looking at jobs/shifts performed inside a particular pay period.
Anyway, without further ado, let's take a look at the monstrosity I have created, and then start breaking it down:
DATEADD(
DATEADD(
DATEADD(
DATETIME_PARSE(
"1/4/"&DATETIME_FORMAT(SET_TIMEZONE(Start, 'Australia/Melbourne'),'GG')
),
IF(
MOD(
DATETIME_FORMAT(SET_TIMEZONE(Start, 'Australia/Melbourne'), 'W'),
2
)=1,
DATETIME_FORMAT(SET_TIMEZONE(Start, 'Australia/Melbourne'), 'W')+2,
DATETIME_FORMAT(SET_TIMEZONE(Start, 'Australia/Melbourne'), 'W')+1
)
-1,'weeks'
),
'-'&
WEEKDAY(
DATEADD(
DATETIME_PARSE(
"1/4/"&DATETIME_FORMAT(SET_TIMEZONE(Start, 'Australia/Melbourne'),'GG')
),
IF(
MOD(
DATETIME_FORMAT(SET_TIMEZONE(Start, 'Australia/Melbourne'), 'W'),
2
)=1,
DATETIME_FORMAT(SET_TIMEZONE(Start, 'Australia/Melbourne'), 'W')+2,
DATETIME_FORMAT(SET_TIMEZONE(Start, 'Australia/Melbourne'), 'W')+1)
-1,'weeks'
)
),
'day'
),
3,'day'
)
...yeah. Certainly not the cleanest solution, but after trying all the others I was stoked to finally discover something that worked for everything, or seemed to (so far).
How to break down what's happening? Well there is a lot of repeating patterns in there, so let's talk about the inner-most function SET_TIMEZONE. On it's own it looks like this:
SET_TIMEZONE(Start, 'Australia/Melbourne')
We're simply taking our reference date field and instructing Airtable to recognise where it is displayed. So everywhere I would otherwise have put in my "Start" field, I have now inserted the whole SET_TIMEZONE function instead. Simple.
The next one is "DATETIME_FORMAT". The first time it appears it looks like this:
DATETIME_FORMAT(SET_TIMEZONE(Start, 'Australia/Melbourne'),'GG')
where 'GG' is the ISO year of the date referenced. That's it for this one, it returns a year like "22" or "23" etc. Among my playing I found if I didn't pull the year from the original job date then it was lost in future calculations, and the current year was subbed in instead. In particular, I had a job on, say, 27/11/2022 being paid on 4/12/2023... more than a year late. Not ideal obviously!
Moving on to the DATETIME_PARSE formula:
DATETIME_PARSE(
"1/4/"&DATETIME_FORMAT(SET_TIMEZONE(Start, 'Australia/Melbourne'),'GG')
),
This one is really interesting. We're finding the very first week of the year in question. Since week "1" of any given year always contains the 4th of January, we can use that date to identify it. Americans might notice that I switched back to using M/D/Y format for this one as it's quicker to not keep forcing Airtable to calculate in my preferred format. Anyway, the output of this formula is the 4th of January in the year that the job was performed. More importantly for our purposes, the output is "Some day (doesn't matter which) inside week 1 of the year the job was performed".
Next, we can use that guaranteed-some-day-inside-week-1 to calculate "some-day-inside-the-pay-week". We do that with the first DATEADD formula:
DATEADD(
DATETIME_PARSE(
"1/4/"&DATETIME_FORMAT(SET_TIMEZONE(Start, 'Australia/Melbourne'),'GG')
),
IF(
MOD(
DATETIME_FORMAT(SET_TIMEZONE(Start, 'Australia/Melbourne'), 'W'),
2
)=1,
DATETIME_FORMAT(SET_TIMEZONE(Start, 'Australia/Melbourne'), 'W')+2,
DATETIME_FORMAT(SET_TIMEZONE(Start, 'Australia/Melbourne'), 'W')+1
)
-1,'weeks'
),
Where the previously used 'GG' was used I have now put in 'W', which pulls the ISO week number of the date referenced, instead of the year. If I was paying weekly that's all I would need. But because my Payroll is fortnightly, I'm either adding +2 weeks to my job date, or +1 week. So I use MOD to figure out the remainder after dividing the week number by 2. If the remainder is 1 (it's an odd week) we add 2 weeks to find the pay week, if the remainder is 0 (even) we add just 1 week. Finally, remembering that we started from week 1 in the year in question and not week 0 (doesn't exist), we need to remove that week at the end:
DATEADD(somedateinsidepayweek, -1 ,'weeks')
Whew! So what have we achieved? We started with the 4th of January, or in other words some-day-guaranteed-to-be-inside-WEEK-1-of-the-year-that-the-job-was-done, and added a number of weeks to arrive at some-day-guaranteed-to-be-inside-the-payroll-week. But because we have no idea what day of the week the 4th of January was, we need to start playing with those results to (1) identify the day of the week we have, (2) move the "day" part back to Monday, and (3) move forward again to the Wednesday I am finally looking for. That is what the second half of the formula is doing:
DATEADD(
{AllThatWorkWeHaveDoneBefore},
'-'&
WEEKDAY(
DATEADD(
DATETIME_PARSE(
"1/4/"&DATETIME_FORMAT(SET_TIMEZONE(Start, 'Australia/Melbourne'),'GG')
),
IF(
MOD(
DATETIME_FORMAT(SET_TIMEZONE(Start, 'Australia/Melbourne'), 'W'),
2
)=1,
DATETIME_FORMAT(SET_TIMEZONE(Start, 'Australia/Melbourne'), 'W')+2,
DATETIME_FORMAT(SET_TIMEZONE(Start, 'Australia/Melbourne'), 'W')+1
)
-1,'weeks'
)
),
'day'
),
which can be re-written for smart humans (who can remember prior calculations) like this:
DATEADD(
SomeDayGuaranteedToBeInsideThePayrollWeek,
'-'&
WEEKDAY(
SomeDayGuaranteedToBeInsideThePayrollWeek
),
'day'
),
Or in other words, taking some random date inside our pay week, and taking out the number of days into that week we are. Hence, we are now at the start of the week. Whether Airtable thinks that Start-of-the-week is a Sunday or a Monday, I have completely lost track of at this point. But it hardly matters because there is only one more step needed to arrive at my Wednesday. You guessed it: add 2 (maybe 3) days to the date we have:
DATEADD(
StartOfThePayWeek,
3,'day'
)
And that's it! What a convoluted mess.
I want to close with some things I'm still not entirely sure I understand properly, maybe someone else is keen to clarify:
- using 'GG' or one of the other format specifiers to find the 2-digit year. What "week year" means or how it differs from the actual year I don't know. And how ISO week year calculates this differently I also don't know. Or why not just use "YY" or one of the formats that return 4 digits instead of 2? I honestly am not sure. Homework: try a date right at the end of 1999 and see how it behaves. The poor employee who took that shift could potentially be paid a century late, who knows?! ** edit: turns out it works, see below for proof. Why? I dunno ¯\_(ツ)_/¯
- was it required to put ALL of the references to my "Start" date inside a SET_TIMEZONE() function? I'm not sure, but once I started it was easy enough to copy/paste to make sure.
- why did I add 1 or 2 weeks to the job's week number, only to remove one of them straight afterwards with the first (innermost) DATEADD formula? Instead couldn't I take into account that we'd started in the first week of the year and add either 0 or 1 week, reducing the number of formula nests by one? I tried making this change and on the first attempt got errors and on the second attempt all dates in my column had the exact same date, so I gave up quickly.
- how the "Use the same time zone for all collaborators" switch in the preferences of Airtable's date field affects the actual date used in calculations. And which one is used in API calls? And all the other places I use calculations to manipulate dates. After realising that leaving the option off seemed to work for most of my situations so far, I'm reluctant to turn it back on for fear of breaking some other automation that has been working well for years.
And finally, here's a quick sheet I made that shows this working:
https://airtable.com/shrjD8QhNxkkLU1QO
