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
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.