Forum Discussion
Solved
Formula to include EOM calculation to specific dates
The formula I am attempting requires validating payments assigned and received. The project requires that there are 3 payments assigned and to be received within the assigned calendar month. For instance, if the payment is assigned for 2/1/23, the payment should be received no later than 2/28/23. It started out as a simple formula that if the received date is less than or equal to the assigned date than add 30, but that does not satisfy if the month is less than or greater than 30 days. We thought about adding additional columns to calculate days using EOM, but that would be too busy. The thought now is to utilize a matrix that shows the number of days in each month to add to the formula. I need assistance with tying the matrix to the formula or would be happy with additional suggestions.
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.
4 Replies
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.
- @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.
