Increasing "00x" in calc code based on 1st and 2nd half of every month?

I have a billing invoice where I need to have a code and the last three digits incrementally increased by +1 every half of every month.

First billing period is 03/16/24-03/31/24 which would generate code ACE-2024-001 based on this formula.

=IF(A2="","","ACE-"&YEAR(A2)&"-"&TEXT(MONTH(A2)-(DAY(A2)<16)-2,"000"))

problem is, this only seems to +1 those last three digits every 31 days. Example for how it should be:

03/16/24-03/31/24 = "ACE-2024-001"

04/1/24-04/15/24 = "ACE-2024-002"

04/16/24-04/30/24 = "ACE-2024-003"

etc..

Here is my worksheet with my work in column B and expected result in column C

Solution

This formula will exactly match the desired results you show:

=arrayformula(let( 
  prefix, "ACE-" & year(A2:A) & "-", 
  seq, 2 * (month(A2:A) - month(A2)) + (day(A2:A) > 15), 
  if(isnumber(A2:A), prefix & text(seq, "000"), iferror(ø)) 
))

See arrayformula(), let(), Boolean arithmetic and your sample spreadsheet.

In the event you want to continue the series in the same tab over several years, I would recommend that you start the series from 1 January rather than from 16 March. To do that, remove the - month(A2) bit.