Between

[Donna]

I am stuck. Columna A are dates and I need column B to be
a formula that can be copied down to give me the correct
info.

Example: if A is between 1/1/04 and 3/15/04 then 1, if
A is between 3/16/04 and 6/15/04 then 2, if A is 6/16/04
between 9/15/04 then 3, if A is 9/16/04 between 1/31/04
then 4.

Can you help?? Please??


A B
1/1/04
2/15/04
3/22/04
4/16/04
9/16/04

 

[Frank Kabel]

Hi
your last boundary is probably 12/31/04 and not 1/31/04?#
try the following in B1
=IF(AND(A1>=DATE(2004,1,1),A1<=DATE(2004,12,31)),IF
(A1<=DATE(2004,3,15),1,IF(A1<=DATE(2004,6,15),2,IF(A1<=DATE
(2004,9,15),3,4))),"")

 

[JE McGimpsey]

One way:

=IF(AND(A1>=DATE(2004,1,1),A1<=DATE(2004,12,31)),1+(A1>DATE(2004,3,15))+(
A1>DATE(2004,6,15))+(A1>DATE(2004,9,15)),"")

 

[Ron Rosenfeld]

I am stuck. Columna A are dates and I need column B to be
a formula that can be copied down to give me the correct
info.

Example: if A is between 1/1/04 and 3/15/04 then 1, if
A is between 3/16/04 and 6/15/04 then 2, if A is 6/16/04
between 9/15/04 then 3, if A is 9/16/04 between 1/31/04
then 4.

Can you help?? Please??


A B
1/1/04
2/15/04
3/22/04
4/16/04
9/16/04

Well, if your last category is ended with a typo, then:

=MATCH(A1,{37987;38062;38154;38246},1)

(The numbers in the array constant represent the dates).


--ron

 

[Harlan Grove]

...
...

=IF(AND(A1>=DATE(2004,1,1),A1<=DATE(2004,12,31)),...

...

Wouldn't YEAR(A1)=2004 be equivalent?

 

[Harlan Grove]

...
...

Well, if your last category is ended with a typo, then:

=MATCH(A1,{37987;38062;38154;38246},1)

(The numbers in the array constant represent the dates).

...

Nonintuitive! Also not generic with respect to date system (i.e., it'll fubar
under 1904 date system). Combining the ideas from the other responses,

=LOOKUP(A1,--({-1E300,"2004-01-01","2004-03-16","2004-06-15","2004-09-16",
"2005-01-01"}),{"",1,2,3,4,""})

 

[Frank Kabel]

...
..

Wouldn't YEAR(A1)=2004 be equivalent?

yes seems to be..
Regards
Frank

 

[Donna]

Thanks for you help, but now my boss wants me to change
the 1,2,3,or 4 to JAN, MAR, MAY, OR AUG. Can you help?

Thanks again for all your help

 

[Ron Rosenfeld]

Nonintuitive! Also not generic with respect to date system (i.e., it'll fubar
under 1904 date system). Combining the ideas from the other responses,

=LOOKUP(A1,--({-1E300,"2004-01-01","2004-03-16","2004-06-15","2004-09-16",
"2005-01-01"}),{"",1,2,3,4,""})

Or, to make it applicable to all years (except 1900)

=MATCH(A1-DATE(YEAR(A1),1,0)-(MONTH(DATE(YEAR(A1),2,29))=2),{0,75,167,259},1)




--ron

 

[Frank Kabel]

Hi
try
=IF(Year(A1)=2004,IF(A1<=DATE(2004,3,15),"Jan",IF(A1<=DATE(2004,6,15),"
Mar",IF(A1<=DATE
(2004,9,15),"May","Aug"))),"")

or adapting Harlan's solution:
=LOOKUP(A1,--({-1E300,"2004-01-01","2004-03-16","2004-06-15","2004-09-1
6",
"2005-01-01"}),{"","Jan","Mar","May","Aug",""})

 

[Harlan Grove]

...
...

Or, to make it applicable to all years (except 1900)

=MATCH(A1-DATE(YEAR(A1),1,0)-(MONTH(DATE(YEAR(A1),2,29))=2),{0,75,167,259},1)

You're hardcoding that which shouldn't be hard-coded, the constant array. If the
OP's boss makes another change (not likely, but suppose), how easy would it be
to change your constant array rather than a constant array of date strings?

This also doesn't trap errors, e.g., A1<0 or >DATE(9999,12,31).

But ignoring errors, how about

=MATCH(A1,--(YEAR(A1)&{"-01-01","-03-15","-06-15","-09-15"}))

 

[Ron Rosenfeld]

=MATCH(A1,--(YEAR(A1)&{"-01-01","-03-15","-06-15","-09-15"}))

In general I like your approach. But I tend to keep away from strings in
representing dates in formulas, feeling that the DATE worksheet function has
less problems with ambiguity. However, it seems your representation may avoid
that.

One minor change in your formula to fit the OP's request:

=MATCH(A1,--(YEAR(A1)&{"-01-01","-03-16","-06-16","-09-16"}))

And, finally, to meet the OP's second request:

=CHOOSE(MATCH(A1,--(YEAR(A1)&{"-01-01","-03-16","-06-16","-09-16"})),"JAN","MAR","MAY","AUG")


--ron

 

[Donna]

Thank to all for your help! Have a great day!

-----Original Message-----