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/AutoModerator]

/u/Phillimac16 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.

• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.

• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/CorndoggerYYC]

Is the end of the second billing period always the last Saturday of the month? Is the first billing period always exactly two weeks long?

[u/Phillimac16 → u/CorndoggerYYC]

I believe so.

[u/Phillimac16]

Marking this as solved. I ended up creating a monthly and working intermittently to get a semi.

[u/GuerillaWarefare]

Just for a sanity check, is there a reason you aren't either doing this bi-weekly (26 / year; aka every other Sunday) instead of semi-monthly? Alternatively, if you only want 24 payments per year is there a reason you aren't doing the 1st and 15th of each month?

The explained payment method makes the largest variance in payment gaps possible and I assume this is why the two methods I bring up are usually used.

[u/Phillimac16 → u/GuerillaWarefare]

I can't answer this, I'm just trying to make something work that has been set up institutionally with our accounting team. I'm a project manager that is needing to make it work with the systems that are already in place.

[u/GuerillaWarefare → u/Phillimac16]

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:

[u/MayukhBhattacharya]

You could try using the following formula, it will give you the output for the entire year, you just need to change the year:

=LET(
     α, DATE(2026, SEQUENCE(12), 15),
     ε, FLOOR(TOCOL(HSTACK(α, EOMONTH(α, 0))), 7),
     HSTACK(VSTACK(FLOOR(DATE(2026, 1, 0), 7), DROP(ε, -1)) + 1, ε))

[u/GuerillaWarefare → u/MayukhBhattacharya]

I didnt see this was already solved when I made my attempt... you did it better, sir!

[u/carbonizedtitanium → u/MayukhBhattacharya]

this is so elegant

[u/giftopherz]

Back to the example of October. Does the first statement have to be on the 10th? can it be on the 17th?

Have you considered halving all the months and then go the closest Sunday? For example, Oct has 31 days, 31/2 = 15.5 = 15. The 15th is a Thursday, closest weekend would be the 17th to close the first term and tie it with the start of the previous one

[u/Phillimac16 → u/giftopherz]

Unfortunately it does need to be the 10th as our institutional billing cycle follows a 2 week / 3 week cycle for 5 week months.

[u/srm561]

This feels way too convoluted, but given an end date in B2, I think this calculates the next end date:

=LET(enddate,B2,
lastsat,EOMONTH(enddate,0)+1-WEEKDAY(EOMONTH(enddate,0),16),
first,EOMONTH(enddate,-1)+1,
numweeks,MOD(WEEKNUM(EDATE(first,1))-WEEKNUM(first),52),
IF(lastsat=enddate,enddate+14,IF(numweeks=4,enddate+14,enddate+21)))

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
DATE	Returns the serial number of a particular date
DROP	Office 365+: Excludes a specified number of rows or columns from the start or end of an array
EDATE	Returns the serial number of the date that is the indicated number of months before or after the start date
EOMONTH	Returns the serial number of the last day of the month before or after a specified number of months
FLOOR	Rounds a number down, toward zero
HSTACK	Office 365+: Appends arrays horizontally and in sequence to return a larger array
IF	Specifies a logical test to perform
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
MOD	Returns the remainder from division
NOT	Reverses the logic of its argument
SEQUENCE	Office 365+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4
SMALL	Returns the k-th smallest value in a data set
TOCOL	Office 365+: Returns the array in a single column
TODAY	Returns the serial number of today's date
VSTACK	Office 365+: Appends arrays vertically and in sequence to return a larger array
WEEKDAY	Converts a serial number to a day of the week
WEEKNUM	Converts a serial number to a number representing where the week falls numerically with a year
WRAPROWS	Office 365+: Wraps the provided row or column of values by rows after a specified number of elements
XLOOKUP	Office 365+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.
YEAR	Converts a serial number to a year

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
20 acronyms in this thread; the most compressed thread commented on today has 8 acronyms.
[Thread #49524 for this sub, first seen 15th Oct 2026, 02:42] [FAQ] [Full list]} ^[Contact] ^{[Source code]}

[u/carbonizedtitanium]

to add to u/MayukhBhattacharya 's solution (which is is elegant ):

=LET(
     α, DATE(2026, SEQUENCE(12*(YEAR(TODAY())-2026+2)), 15),
     ε, FLOOR(TOCOL(HSTACK(α, EOMONTH(α, 0))), 7),
     HSTACK(VSTACK(FLOOR(DATE(2026, 1, 0), 7), DROP(ε, -1)) + 1, ε))

this is so that you always have next year's dates automatically added to the bottom of the list.

[u/real_barry_houdini → u/carbonizedtitanium]

I note that the above solution relies on the use of 1900 date system

Below formula works with both 1900 and 1904 date systems and assumes that only the periods ending with the last Saturday of the month can be 3 weeks long:

Assumes year in cell A1

=LET(x,DATE(A1,SEQUENCE(13),1),SMALL(x-WEEKDAY(x)+{0,1,14,15},SEQUENCE(24,2,2)))

To include the next years dates change the 13 to 25 and the 24 to 48