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.
It is difficult for us to debug problems based on images alone. "If a picture is worth a thousand words, an Excel file is worth a thousand pictures". :wink:
Attach a (redacted) Excel file that demonstrates the problem. Click "browse" at the bottom of the reply window. If you cannot attach a file, upload the file to a file-sharing website and post the download URL.
Obviously, M5 does not equal AG4, despite appearances.
If both M5 and AG4 are text, they might differ by "invisible" characters.
Assuming that both M5 and AG4 are numeric, they might be differ dates, even if they appear to be the same month and year due to formatting. For example, M5 might be 9/1/2023 and AG4 might be 9/30/2023.
And of course, M5 might be text and AG4 might be a numeric date formatted as mmm-yy, or vice versa.
Use ISTEXT to distinguish text from numeric dates. Looks can be deceiving; and the format of the cell does not matter.
-----
PS.... It is better to return the null string ("") instead of a string of one or more spaces (" "). With the null string, conditions like IF(A1="", ...) work as intended whether A1 is empty (no value) or the null string.