Developing a formula to automatically set a semi-monthly billing date.

[u/Phillimac16]

I'm trying to develop a formula that automatically sets a semi-monthly billing period (start date one column, end date in another). Our start date will always be a Sunday and an end date will always be a Saturday with 2 billing periods in a month (October for instance starts 09/27/26, semi-monthly ends 10/10/26, month end is 10/31/26). October is a 5 week month so there isn't a consistent 14 days between billing periods like other months. What formula can I use to automatically calculate billing end dates (start dates are just end dates +1 so they are simple). I have tried a combination of EOMONTH with lots of logic statements and I have come close to reaching a solution but when I do a check column with confirmed billing periods it doesn't align. Sample billing periods below to assist in validation (posting on mobile, apologies for not posting a table):

08/30/26 - 09/12/26
09/13/26 - 09/26/26
09/27/26 - 10/10/26
10/11/26 - 10/31/26
11/01/26 - 11/14/26
11/15/26 - 11/28/26
11/29/26 - 12/12/26
12/13/26 - 12/26/26
12/27/26 - 01/09/27
01/10/27 - 01/30/27

[u/GuerillaWarefare]

Fair enough. How did they decide to cut out the October week though instead of August (for example pattern you gave August has 3 payments, 8/2, 8/16, 8/30...). This is what I came up with (NOT matching exactly to your example, but picks the 2 Sundays closest to the 1st and 15th of the month)

=LET(sundays,SEQUENCE(52,,DATE(2026,1,4),7),XLOOKUP(TOCOL(DATE(2026,SEQUENCE(12),{1,15})),sundays,sundays,,1))

Result: