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!

Forum Discussion

Multiple IF statement for a date range using Month functions

Resources