Multiple IF statement for a date range using Month functions

[Databoy01]

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

[#2]

@Databoy01 

Does this do what you want?

=IFERROR(MONTH(EDATE(C76,--(DAY(C76)>=15))),"Error")

[#3 · Accepted solution]

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

[#4]

@mathetes 

How can DAY(some_date) be <=0 ?

[#5]

This works perfectly! And I did use the IFERROR version as that insures the correct date is always inputted too. THANK YOU!

[#6]

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.

[#7]

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?