Payroll Schedule

[jalizmaldonado]

Hi, I'm working on a workflow to determine a payroll schedule based on whether it's a monthly, semi-monthly, bi-weekly, or weekly frequency. I was able to do the weekly and monthly formulas but do not know how to write a formula to alternate between payment dates based on the following rules:

semi-monthly - 15th and the last day of the month as long as it's not on a weekend or holiday, otherwise it's the day before

bi-weekly - every other Friday, starting on the first Friday, as long as it's not a holiday, otherwise it's the day before

24 Payments	27 Payments
Semi-Monthly	Bi-Weekly
2022-01-15	2022-01-06
2022-01-30	2022-01-20
2022-02-15	2022-02-03
2022-02-28	2022-02-17
2022-03-15	2021-03-03
2022-03-31	2022-03-17
2022-04-15	2022-03-31
2022-04-30	2022-04-14
2022-05-15	2022-04-28
2022-05-31	2022-05-12
2022-06-15	2022-05-26
2022-06-30	2022-06-09
2022-07-15	2022-06-23
2022-07-31	2022-07-07
2022-08-15	2022-07-21
2022-08-31	2022-08-04
2022-09-15	2022-08-18
2022-09-30	2022-09-01
2022-10-15	2022-09-15
2022-10-31	2022-09-29
2022-11-15	2022-10-13
2022-11-30	2022-10-27
2022-12-15	2022-11-10
2022-12-31	2022-11-24
	2022-12-08
	2022-12-22
	2022-12-29

Please let me know if you're able to assist.

[Emil_Kos · Accepted solution]

Hi @jalizmaldonado,

I am not sure if this is me but your table might be incorrect. 

The bi-weekly part isn't working correctly as I didn't know what is the starting date. You will need to remove half of the weeks but I think you will be able to figure it out

[apathetichell · Accepted solution]

 

Good amount of complexity here...

 Attached a cleaner version - first version accounted for the opening row in biweekly as having a thrusday paydate because it's the opening row and doesn't have a start date - this version filters that null() out

[jalizmaldonado]

@Emil_Kos thank you so much. Working on the skipping every other row part now.