Try using the following to generate your fortnight number:
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:
)&' – '&
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