Rounding a Date down if it doesn't exist in Month

J-Des000 Alternatively, you could use the DATE function to return the last day of the month:

=DATE(LEFT(A2,4), MID(A2,6,2) + 1, 0)

Note: day 0 is interpreted as the last day of the previous month, which is why we add 1 to the current month.

Furthermore, if your series does not exclusively consist of month-end dates, you could use the MIN function as follows:

=MIN(DATE(LEFT(A2,4), MID(A2,6,2) + 1, 0), DATE(LEFT(A2,4), MID(A2,6,2), RIGHT(A2,2)))

For example, if the text string was 2024-02-15, the formula would return the minimum between 02/29/2024 and 02/15/2024. Likewise, if the text string was 2024-02-31, the formula would return the minimum between 02/29/2024 and 03/02/2024 (because day 31 is 2 days beyond the last day of February 2024, so it's interpreted as March 2, 2024).

Sample Results

Also, with Excel for MS365, you could spill the results for the entire series using the MAP function. For example:

=MAP(A2:A11, LAMBDA(str, LET(y, LEFT(str,4), m, MID(str,6,2), MIN(DATE(y, m + 1, 0), DATE(y, m, RIGHT(str,2))))))

EDIT: another variant without the need for MAP...

=LET(
    arr, A2:A11,
    y, LEFT(arr, 4),
    m, MID(arr, 6, 2),
    date1, DATE(y, m + 1, 0),
    date2, DATE(y, m, RIGHT(arr, 2)),
    IF(date1 < date2, date1, date2)
)

Cheers!