Categorizing data in Excel using formula -

Categorizing data in Excel using formula -

i trying classify each row x, y, or z based on both month , day (a time of year). categories are:

x = june 1st - oct 1st y = nov 15th - apr 15th z = others

i realize may simple, can't seem formula right. table have looks like:

day  month  year  test 7      5          2012   z 6      5          2013   z 2      11        2011    z 4      6          2013   z 27    5          2013   z 14    3          2013   z 14    5          2014   z 20    10        2013   z 5      12        2013   z

my current formula "test" is:

=if(and(and(b2>=6,a2>=1),and(b2<=10,a2<=1)),"x",if(or(and(b2<=4,a2<=15),and(b2>=11,a2>=15)),"y","z"))

any help appreciated!

the reason formula isn't working testing both month , day @ same time - example,

if(and(b2<=4,a2<=15)

will homecoming false march 16, though want true. because formula doesn't understand concept of "days , months".

the formula utilize is

=if(and(date(c2,b2,a2)>=date(c2,6,1),date(c2,b2,a2)<date(c2,10,1)), "x", if(or(date(c2,b2,a2)>=date(c2,11,15),date(c2,b2,a2)<date(c2,4,15)), "y", "z"))

note - added carriage homecoming shouldn't there create more readable.

note formula computes date(c2,b2,a2) number of times, , might have separate (hidden) column. nice thing look uses built in date function understands months , days - , it's relatively easy prepare depending on date range criteria are. test dates, gives following:

day month year test 7 5 2012 z 6 5 2013 z 2 11 2011 z 4 6 2013 x 27 5 2013 z 14 3 2013 y 14 5 2014 z 20 10 2013 z 5 12 2013 y

note - updated after @pnuts pointed out error...

also note... there total of 4 conditions in above - , took liberty in deciding dates include. basically, translated requirements follows:

date >= nov 15 of year ==> "y" date < apr 15 of year ==> "y" date >= june 1 , date < oct 1 ==> "x" else "z"

this may not exactly wanted should obvious how expressions work, , should able adjust needed (in particular not clear if 15th of apr should part of "y" or "z", example.)

i think useful define series of variables (maybe on hidden sheet)

startxmonth = 4 startxday = 15 stopxmonth = 10 stopxday = 1

etc, , can utilize these names instead of "magic numbers" in formula. way, if decide want alter range of dates corresponding particular tariff, can alter in sensible place, rather somewhere buried in formula. may think work now, give thanks me later...

finally, might want create dates "up , including" - using <= , >=, , defining first , lastly day (rather than, above, first day include , first day not include).

let me know if makes sense.

excel excel-formula conditional-statements