Forum Discussion

Copper Contributor

Aug 30, 2023

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

excel

mathetes
Aug 30, 2023
Databoy01

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")

mathetes

Silver Contributor

Aug 30, 2023

Databoy01

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")

Databoy01
Copper Contributor
Aug 30, 2023
This works perfectly! And I did use the IFERROR version as that insures the correct date is always inputted too. THANK YOU!
HansVogelaar
MVP
Aug 30, 2023
mathetes

How can DAY(some_date) be <=0 ?
- mathetes
  Silver Contributor
  Aug 30, 2023
  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?
- Databoy01
  Copper Contributor
  Aug 30, 2023
  As far as my limited knowledge of Excel. If nothing is in the cell, excel considers that a 0 for the date. As for = That would be If i have a 0 in that field for some reason. If that is what I would guess. Plus thank you also for trying to help me. I will try your formula as well to see how it performs this as well. Again thanks.