Forum Discussion
Formula to include EOM calculation to specific dates
Starting with Excel
20212003, Excel includes the EOMONTH function which calculates the end of the month of its first parameter (or the end of a different month, depending on the second parameter). No additional column is required.Unless your column M formulas have some logic as to which single Received column to compare with which single Assigned column, it appears that you should be checking three Date Rcvd values per row; so your formula for M3 might be:
=IF( AND(G3<=EOMONTH(A3,0), I3<=EOMONTH(C3,0), K3<=EOMONTH(E3,0)), "Yes", "No" )(with additional arguments to the AND function if you also need to compare amounts).
And if that is true, because your cells A6 and C6 refer to 2022, while cells G6 and I6 refer to 2023, M6 should show "No", in spite of the on-time Dec 2022 payment.
If your version of Excel is older than 2021, the VLOOKUP function can be used, using the result of a TEXT function as its lookup argument. But then you would need extra logic to handle February in leap years.
Starting with Excel 2021 2003, Excel includes the EOMONTH function which calculates the end of the month of its first parameter (or the end of a different month, depending on the second parameter). No additional column is required.
Unless your column M formulas have some logic as to which single Received column to compare with which single Assigned column, it appears that you should be checking three Date Rcvd values per row; so your formula for M3 might be:
=IF( AND(G3<=EOMONTH(A3,0), I3<=EOMONTH(C3,0), K3<=EOMONTH(E3,0)), "Yes", "No" )
(with additional arguments to the AND function if you also need to compare amounts).
And if that is true, because your cells A6 and C6 refer to 2022, while cells G6 and I6 refer to 2023, M6 should show "No", in spite of the on-time Dec 2022 payment.
If your version of Excel is older than 2021, the VLOOKUP function can be used, using the result of a TEXT function as its lookup argument. But then you would need extra logic to handle February in leap years.
- @SnowMan44 thank you for your feedback and this worked!
Actually, EOMONTH has been available since at least Excel 2003...
- OK, good. When I loaded the documentation page earlier, the "More…" versions link did not appear.
