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
As JoeUser2004 has already pointed out, your dates are not what you think they are. I'd agree with him that you need to fix those first. Personally I'd recommend using the "Short Date" format. That way you'll be getting better results.
When you apply that format to your column L you'll see that because you entered your dates as shown here:
they actually are registered as follows:
This is because Excel thinks it's being helpful (and it is, if you know that's what its doing) by adding a year to things when the user enters only the month & day, but it always assumes you, the user, are wanting the current year. So your Jun-23 became 6/23/22 in the underlying number, and so forth: what you intended as future years ended up as dates in 2022, some of which preceded the start date!!
So let me ask you to do three things:
- Fix the dates so they mean what you want them to mean.
- Re-post a file, and do so in a manner that allows us to edit, not just view. I had to copy the whole thing to my own computer in order to see what the underlying issues were. Post a copy of your original so whatever changes we make don't affect your original.
- By the way, if those are real names of real people, remove them too.
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.
