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
Almost exactly as I had guessed(!): M5 is 9/30/2023, whereas AG4 is 9/1/2023.
To see that, temporarily format both cells as Custom m/d/yyyy.
IMHO, the simplest and best fix is to use dates consistently: always the 1st of each month (best, IMHO) or always the end of each month (which varies from 28 to 31 :sigh:).
PS.... On second thought, I want to emphasize just how bad it is to use the end of the month. Then "the end of Feb 2020" (2/29/2020) is not equal to "the end of Feb 2021" (2/28/2021) in its simplest form. Of course, there are ways to make the comparison work, if necessary.
If you "cannot" do that, we can offer other (much more complicated) suggestions to make the comparisons work as intended.