Thursday, September 8, 2022

Create Bi-monthly Pay Calendar Events

Someone I know is paid on a Bi-monthly schedule, aka Semi-Monthly, and wanted some help on having reminders in their calendar. A Bi-monthly schedule is generally where a person is paid on the 15th and the last business day of the month. Its more about the bank ETF processing business days than the firms days of operation. The issue is when these fall on a weekend the payday processing is shifted forward to Friday. I used to be on a pay schedule like this myself way back and was slightly annoyed by not being able to have a calendar item automatically address it. July, August, and February (Leap years!)  made end of month recurrances difficult.My friend uses Google Workspace so my focus was on that but this works with Outlook and others.

So how can we automate this? Google Workspace (also Gmail) nor Outlook natively allows you to shift events around based on conditions such as weekends. You pick the 15th of the month or the third Tuesday, that type of repeating. In this case, we not only have weekends but also for thethe  last day of month with it ending on either 30 or 31 and of course, there is February ending on the 28th with the occasional leap year along with maybe a phase of the moon in there somewhere.

Luckily there is a standard for it that can facilitate way more advanced functionality than even what I'm doing here. Its called iCalendar and its covered by RFC5545 and supplemented by RFC7986. When you export calendar events from Google Workspace/Gmail, Outlook, and most anything else it uses this standard via the .ics file format. This replaced the former VCalendar (.vcs) format.

With all of its power, I was unable to get it into one file so there is one for each pay cycle during the month. Therefore one for the 15th and one for the end of the month. I parsed all sorts of developer documents and such around the fields within an iCal file as well as exporting several of my calendar entries to understand the syntax. I know way more than I ever need to know about this. So lets start with what should be the easier one, the first one on the 15th:

 BEGIN:VCALENDAR  
 VERSION:2.0  
 BEGIN:VEVENT  
 RRULE:FREQ=MONTHLY;INTERVAL=1;BYSETPOS=-1;BYMONTHDAY=13,14,15;BYDAY=MO,TU,WE,TH,FR  
 SUMMARY:My Payday  
 DTSTART;VALUE=DATE:20180430  
 SEQUENCE:0  
 DESCRIPTION:  
 END:VEVENT  
 END:VCALENDAR  

The key is the RRULE (Repeat Rule) line. Let's break it down, however, they can pivot off each other so definitions can change based on what attributes are present and there are many other attributes that can be a part of the RRULE. Confusing right? So I have it listed more from a flow perspective since they build on each other which makes more sense to me.

  • FREQ - This is telling it to do a monthly repeat
  • INTERVAL - This says how many times a month, in this case we want once
  • BYDAY - What days will this apply? Business days of Monday to Friday and exclude Saturday and Sunday.
  • BYMONTHDAY - What calendar date can it apply? If Monday to Friday is the 15th then create on the 15th. If the 15th is Saturday then Friday is the 14th so create on that as it matches BYDAY of Friday. If the 15th is Sunday then Friday is the 13th so use that as it matches BYDAY of Friday.  INTERVAL controls how many events can be created.
  • BYSETPOS - This is the nth instance once you calculated the other attributes. BYSETPOS is what tells it to back up to the earlier match of Friday due to BYMONTHDAY also matching vs just skipping this instance that FREQ dictates. Officially this is defined as 'Each BYSETPOS value can include a positive (+n) or negative (-n) integer. If present, this indicates the nth occurrence of the specific occurrence within the set of occurrences specified by the rule.'

The other lines of interest would be the SUMMARY which is the title of the calendar entry and the DTSTART which is when to start the calendar entry. I did this starting on my friend's hire date.

If you want to learn way more about all these fields you can reference this article

I thought the end of the month would be harder but it's actually simpler as we do not need the BYMONTHDAY field. iCalendar defaults to the first day of the month however since the BYSETPOS is negative, that goes back a day which is the last day of the previous month, and then BYDAY dictates the other half of when it can apply.

 BEGIN:VCALENDAR  
 VERSION:2.0  
 BEGIN:VEVENT  
 RRULE:FREQ=MONTHLY;INTERVAL=1;BYSETPOS=-1;BYDAY=MO,TU,WE,TH,FR  
 SUMMARY:My Payday  
 DTSTART;VALUE=DATE:20180430  
 SEQUENCE:0  
 DESCRIPTION:  
 END:VEVENT  
 END:VCALENDAR  

This does not account for any banking holidays nor if the payday policy states if it falls on a Sunday the paydate is moved forward to Monday but it can be modified for this scenario. New years eve is the only one that might come into play.

If you want to make use of this then copy each code block above to a separate text file ending in .ics. Edit as you see fit such as SUMMARY and DTSTART attributes. To import into Google Workspace (or Gmail) you can follow step 2 of this KB. For Outlook you can follow this KB. For other less used calendar programs you can look up the process yourself. For Outlook and Workspace you can only edit the reminders and edit the events except you cannot edit the recurrence part. Note it will use your default reminder settings so you may want to adjust them after you import.

Maybe I will get motivated to see if I can address weekly/bi-weekly pay cycles and holidays such as US Thanksgiving and Christmas.