Date in one cell results value in another

[wmichael]

Hello. I am seeking a formula that will recognize a date in one cell and show the week of the month it is in another cell. Please see the following examples- thank you, Michael.

Date range 1-7= Week 1
Date range 8-14= Week 2
Date range 15-21= Week 3
Date range 22-31= Week 4

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<tbody>[TR]
[TD="align: center"]CURRENT DATE FROM[/TD]
[TD="align: center"]WEEK[/TD]
[/TR]
[TR]
[TD="align: center"]Sun, Nov 12, 2017[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Tue, Nov 14, 2017[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]Sun, Feb 18, 2018[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]Tue, Sep 4, 2018[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]Mon, Nov 12, 2018[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]Sat, Mar 3, 2018[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]Fri, Jan 12, 2018[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]Sat, Mar 3, 2018[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]Sun, May 21, 2017[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]Thu, Feb 1, 2018[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]Tue, Jul 25, 2017[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]Sat, Sep 23, 2017[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]Sat, May 19, 2018[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]Tue, Oct 2, 2018[/TD]
[TD="align: center"]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[Logit]

.
Assuming your dates begin in A1 ... paste this formula in B1 and drag down the column :

=WEEKNUM(A1,1)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),1),1)+1

 

[NiMip]

Using your date ranges, I would use the following and fill down the dates (assuming they start in A2)

=INDEX({1,2,3,4},MATCH(DAY(A2),{1,8,15,22},1))

 

[Tetra201]

A bit shorter:

=LOOKUP(DAY(A2),{1,8,15,22},{1,2,3,4})

 

[wmichael]

.
Assuming your dates begin in A1 ... paste this formula in B1 and drag down the column :

=WEEKNUM(A1,1)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),1),1)+1

This worked Great- thank you!

 

[Logit]

You are welcome.