If Formula is not giving me results i am looking for.

[melaniemcisaac]

I want to assign a week number to correlate with a range of dates.

If the date in cell A5 is between 01/01/2019 and 01/05/2019 I want it to say "1"
If the date in cell A5 is between 01/06/2019 and 01/12/2019 I want it to say "2"
If the date in cell A5 is between 01/13/2019 and 01/19/2019 I want it to say "3"
If the date in cell A5 is between 01/20/2019 and 01/25/2019 I want it to say "4"

If it does not meet any of these, I want it to be blank.

This is the formula I have and it is not giving me what I want

=IF(A5>=1/1/2019<=1/5/2019,"1",IF(A5>=1/6/2019<=1/12/2019,"2",IF(A5>=1/13/2019<=1/19/2019,"3",IF(A5>=1/20/2019<=1/26/2019,"4",""))))

 

[michaeltsmith93]

I wrote a formula like this recently for someone else on MrExcel. My guess is that Excel doesn't accept the syntax in your logical test.<y"<y." use="" and="" instead.
<y"


=IF(AND(A5>=1/1/2019,A5<=1/5/2019),"1",IF(AND(...

And then IFERROR around it to cover values outside the first and last dates, and make the value if error blank.</y"
</y"<y.">

 

[Fluff]

Maybe like
=IF(A5< DATEVALUE("1/1/2019"),0,IF(A5< DATEVALUE("1/6/2019"),1,IF(A5< DATEVALUE("1/12/2019"),2)))

 

[Tetra201]

Here is a shorter way:

=IF(AND(A5>=--"1/1/2019",A5<=--"1/25/2019"),WEEKNUM(A5),"")

 

[Fluff]

Another way
=IFERROR(LOOKUP(A5,DATEVALUE({"1/1/2019","1/6/2019","1/13/2019","1/20/2019","1/26/2019"}),{1,2,3,4,""}),"")