Forum Discussion
Rounding a Date down if it doesn't exist in Month
Hi,
I am trying to round any date in a series that doesn't exist (e.g if 31st doesn't exist in month, round down to 30, if it's 31st in Feb, round down to 29, etc) in a month down.
Ex (Array 1):
2024-02-31
2024-04-31
2024-08-31
2024-09-31
Formula should output the following from Array 1 (e.g rounding down to closest date):
2024-02-29
2024-04-30
2024-08-31
2024-09-30
Any ideas on how this can be done? My thanks in advance.
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!
Obviously, Array1 are texts. And if the 'date pattern' is always yyyy-mm-dd then this would work:
Formula in B2, copied down.
=EOMONTH(DATEVALUE(LEFT(A2,7)&"-01"),0)Just interesting, how Arra1 is generated. If manually, perhaps it's easier to start from 2024-01-31 and drag Fill Months down.
If by formulae like DATE(year, month, 31), it could be changed on =EOMONTH( DATE( year, month ,1), 0)
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!
