Forum Discussion
Solved
Multiple IF statement for a date range using Month functions
Hello, I am trying to obtain a value for my spreadsheet that will say it’s the same month number as the month in the date or the next month if it is after the 15th of that month. Otherwise it will ei...
With multiple conditions, you're actually better off--with a clearer formula--if you use the IFS function instead of nesting multiple IF conditions. This does, I think, what you want.
=IFS(DAY(C76)<=0,"Date->",AND(DAY(C76)>0,DAY(C76)<15),MONTH(EDATE(C76,0)),DAY(C76)>=15,MONTH(EDATE(C76,1)))To break it down logically where each line after the opening IFS( represents a condition and a consequence
=IFS(
DAY(C76)<=0,"Date->",
AND(DAY(C76)>0,DAY(C76)<15),MONTH(EDATE(C76,0)),
DAY(C76)>=15,MONTH(EDATE(C76,1))
)
If you want to cover all bases--to cover such things as a totally false date, eg 8/32/2023, you could embed this in an IFERROR
=IFERROR(
IFS(
DAY(C76)<=0,"Date->",
AND(DAY(C76)>0,DAY(C76)<15),MONTH(EDATE(C76,0)),
DAY(C76)>=15,MONTH(EDATE(C76,1))
),
"bad date")
How can DAY(some_date) be <=0 ?
I was just copying the condition from the original, but, since you asked, we could get philosophical or theological and think about the day before the Big Bang: was it day -1?