Forum Discussion
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 either give an error if incorrect information is provided or enter value with this is the cell “Date->”. Help please
Thanks Ken
=IF((DAY(C76<=0)),"Date-> ","Error"), IF((DAY(C76>0)), MONTH(EDATE(C76,0)), "Date-> "), IF((DAY(C76>=15)), MONTH(EDATE(C76,1)),"Date-> ")
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")
6 Replies
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")
- This works perfectly! And I did use the IFERROR version as that insures the correct date is always inputted too. THANK YOU!
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?
