Forum Discussion
Help with nested IF AND function
Hayche .... I wrote: ``I want to emphasize just how bad it is to use the end of the month``
On second thought, looking at the formula in column L, it is clear that your intent is for columns L and M to contain end-of-month dates, if we can assume that column K always contains first-of-month dates, as it does.
So, the simplest fix might be:
=IF(AND(S4>=$K$5, S4<=$L$5), $H$5/$J$5, IF(EOMONTH(S4,0)=$M$5, $I$5/$J$5, ""))
Alternatively, you could have end-of-month dates in R4:BL4.
But in that case, the formulas in S4:BL4 should be changed from =EDATE(R4,1) to =EOMONTH(R4,1).
(Compare the difference in S4 if R4 is 6/30/2022.)
However, if you change R4:BL4 to end-of-month dates, I don't know how that might affect other dependent formulas in your original Excel file, assuming the attach Excel file is an excerpt.
Thanks for the fast responses and apologies for not posting the excel sheet to begin with.
Here's the link to the file on One Drive: https://1drv.ms/x/s!AhVNGlcJB_F8gYxsT5XBkkCacv0XeQ?e=5alCia
Hayche .... I wrote: ``I want to emphasize just how bad it is to use the end of the month``
On second thought, looking at the formula in column L, it is clear that your intent is for columns L and M to contain end-of-month dates, if we can assume that column K always contains first-of-month dates, as it does.
So, the simplest fix might be:
=IF(AND(S4>=$K$5, S4<=$L$5), $H$5/$J$5, IF(EOMONTH(S4,0)=$M$5, $I$5/$J$5, ""))
Alternatively, you could have end-of-month dates in R4:BL4.
But in that case, the formulas in S4:BL4 should be changed from =EDATE(R4,1) to =EOMONTH(R4,1).
(Compare the difference in S4 if R4 is 6/30/2022.)
However, if you change R4:BL4 to end-of-month dates, I don't know how that might affect other dependent formulas in your original Excel file, assuming the attach Excel file is an excerpt.
- Joe, you're absolutely bang on! I never realized that column M was different to the orange months on the right because of the formatting - was too busy thinking I had made a mistake on the forumla itself. I took your advice and incorportated EOMONTH and the formula now works like a charm.
Thanks for the help, Joe and Mathetes too. I couldn't have done it without you (or it would have taken a whole lot longer anyway!)
Hayche