Too Many "IFs"

[johnnyb5]

I'm working with Julian dates and day counts on a spreadsheet to determine the quantity of days in between posting orders and receiving orders. On my sheet in cell "N4" I have a formula that determines what the current Julian day is (i.e 1 though 365) and that portion is working fine. Below that cell in "N5" I need to determine the day count value for the reporting period which will be 28, 29, 30, or 31. I've developed the formulas but I'm at a loss as to successfully string them together as there are 13 date groups. I'm thinking that there may be a smarter method than to try to put 13 "Ifs" in one formula for inserting in a VBA macro. I've researched a couple of "Else If" statements but nothing as long as the one I'm attempting. Could anyone determine a smart way to assemble the following formulas (each formula works separately) The result of the day counts will be used in additional calculations.
"=IF(AND($N$4 >= 1, $N$4 <=15), 31)"
"=IF(AND($N$4 >= 16, $N$4 <=46), 31)"
"=IF(AND($N$4 >= 47, $N$4 <=74), 28)"
"=IF(AND($N$4 >= 75, $N$4 <=105), 31)"
"=IF(AND($N$4 >= 106, $N$4 <=135), 30)"
"=IF(AND($N$4 >= 136, $N$4 <=166), 31)"
"=IF(AND($N$4 >= 167, $N$4 <=196), 30)"
"=IF(AND($N$4 >= 197, $N$4 <=227), 31)"
"=IF(AND($N$4 >= 228, $N$4 <=258), 31)"
"=IF(AND($N$4 >= 259, $N$4 <=288), 30)"
"=IF(AND($N$4 >= 289, $N$4 <=319), 31)"
"=IF(AND($N$4 >= 320, $N$4 <=349), 30)"
"=IF(AND($N$4 >= 350, $N$4 <=365), 31)"

Will a series of "Else IFs" work for this in VBA???

Thanks,
JB

 

[mikerickson]

This looks like you might want to use a VLookup table rather than a series of IF statements.

 

[Neil_Murray]

=index({31,31,28,31,30,31,30,31,31,30,31,30,31},0,match($n$4,{0,16,47,75,106,136,167,197,228,259,289,320,350},1))

 

[johnnyb5]

Genius, simple genius. I've been struggling for days. I have never seen index & match used in a set like this. I combined your answer with the "Leap Year if formula" and have tested it and it works superbly.
My final formula looks like this:
=IF(OR(MOD(YEAR(TODAY()),400)=0,AND(MOD(YEAR(TODAY()),4)=0,MOD(YEAR(TODAY()),100)<>0)), INDEX({31,31,29,31,30,31,30,31,31,30,31,30,31},0,MATCH($N$4,{0,16,47,76,107,137,168,198,229,260,290,321,351},1)), INDEX({31,31,28,31,30,31,30,31,31,30,31,30,31},0,MATCH($N$4,{0,16,47,75,106,136,167,197,228,259,289,320,350},1)))

Thank You Neil !!!
JB

 

[Neil_Murray]

Glad to help

 

[Jonmo1]

Give this a try

=DAY(EOMONTH(DATE(YEAR(TODAY()),1,$N$4)-15,0))

 

[ZVI]

Just for the info - seems that the IFS function will come soon to Excel 2016.

 

[AlKey]

Just for the info - seems that the IFS function will come soon to Excel 2016.

Just updated mine. The IFS is there! Thanks for the tip.

 

[Jonmo1]

=DAY(EOMONTH(DATE(YEAR(TODAY()),1,$N$4)-15,0))

Actually, if the formula in N4 is just the Julian date based on Today's date.
Then my formula doesn't need to use that cell to convert that Julian date back to a standard date.
It can just use TODAY()

=DAY(EOMONTH(TODAY()-15,0))