Search code examples
excelexcel-formula

Dynamically update a work order # every half month?

column A is a list of days for half of may. column B is a work # calc. I wanted the calc to display ACE-2024-004 for the first half of may, but it's ending in 001 for some reason.

enter image description here

=MAP(A11:A26, LAMBDA(date,
    IF(ISNUMBER(date),
        LET(
            prefix, "ACE-" & YEAR(date) & "-",
            start_date, $A$11,
            date_diff, date - start_date,
            period_num, INT(date_diff / 15) + 1,
            seq, TEXT(period_num, "000"),
            prefix & seq
        ),
        ""
    )
))

my goal is to be able to then change the list of days to 5/16-5/31 and the codes to change to ACE-2024-005 and then when I change to 6/1-6/15, it'll change to ACE-2024-006 and so forth for every half month I change to.

Any idea what I'm doing wrong?

Solution

  • figured it out:

    =MAP(A11:A26, LAMBDA(date,
    IF(ISNUMBER(date),
        LET(
            prefix, "ACE-" & YEAR(date) & "-",
            start_date, DATE(YEAR(date), 5, 1),
            month_diff, (YEAR(date) - YEAR(start_date)) * 12 + (MONTH(date) - MONTH(start_date)),
            period_num, 4 + month_diff * 2 + IF(DAY(date) <= 15, 0, 1),
            seq, TEXT(period_num, "000"),
            prefix & seq
        ),
        ""
    )
))