how to find , in current column containing partial dates of a year, end date of month for a cell?
my use of Xmatch returns 31/08/2021 instead of 29/10/2021 (last date of Oct in my data).
my formula is XMATCH(31/10/2021, B, Exact or next smallest)
Try this (ignore the 'Add a sheet' box):
Formula shown is in selected cell (D2), and returns 9, the row of column B containing October 29, the last entry before the end of October. The search is for the largest date less than or equal to the day before October 31 (EOMONTH(October 1)-1).
Changing the last date (Nov 2) to Oct 31 does not change the result.
Regards,
Barry
Numbers Xmatch how to find last date before EOMONTH?
