Between

  • Thread starter Thread starter Donna
  • Start date Start date
D

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
 
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))),"")
 
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)),"")
 
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
Click to expand...

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
 
...
...
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).
Click to expand...
...

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,""})
 
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
 
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,""})
Click to expand...


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
 
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",""})
 
...
...
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)
Click to expand...

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"}))
 
=MATCH(A1,--(YEAR(A1)&{"-01-01","-03-15","-06-15","-09-15"}))
Click to expand...

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
 
Back
Top