Would anyone know of a shorter way of doing this...

[chazrab]

...maybe with a For loop that would not require repetitive statements with only two unique values that change [i.e., the Case is column number and the date]

Dim d1 As Date
colnum = Round((Date - DateValue("11/16/2016")) / 7, 0)

Select Case colnum
Case Is = 44
     d1 = DateValue("9/17/2017")
Case Is = 45
     d1 = DateValue("9/24/2017")
Case Is = 46
     d1 = DateValue("10/1/2017")
Case Is = 47
     d1 = DateValue("10/8/2017")
Case Is = 48
     d1 = DateValue("10/15/2017")
Case Is = 49
End Select

Thanks for anyone's help.
cr

 

[FDibbins]

Why not just use a vlookup formula and a table containing the values and dates?

 

[RatExcel]

Maybe just:

d1 = DateValue("11/16/2016") + Round((Date - DateValue("11/16/2016")) / 7, 0) * 7 - 3

 

[chazrab]

Maybe just:

d1 = DateValue("11/16/2016") + Round((Date - DateValue("11/16/2016")) / 7, 0) * 7 - 3

..this one line of code looks almost too simple. I can't see it right now. I'm sure you understand it way better than me. Can you please explain the arithmetic operation that would cause colnum to change based on Date and how the correct column number is selected for a 7 day period between each change in the value of colnum?
Thanks
cr

 

[RatExcel]

But why calculating number of weeks and then using hardcoded dates to assign value to d1 if you could figure out the number of weeks

Round((Date - DateValue("11/16/2016")) / 7, 0)

mutliply it by 7 to arrive with number of days

Round((Date - DateValue("11/16/2016")) / 7, 0) * 7

add it to your original date / starting point

DateValue("11/16/2016") + Round((Date - DateValue("11/16/2016")) / 7, 0) * 7

and then just simply subtract 3 to arrive with the desired date

DateValue("11/16/2016") + Round((Date - DateValue("11/16/2016")) / 7, 0) * 7 - 3

?

You don't need to use Select Case, it takes a lot of lines and you need to use hardcoded dates and probably in the future you would have to add next lines in order to make it work.

 

[Rick Rothstein]

...maybe with a For loop that would not require repetitive statements with only two unique values that change [i.e., the Case is column number and the date]

Dim d1 As Date
colnum = Round((Date - DateValue("11/16/2016")) / 7, 0)

Select Case colnum
Case Is = 44
     d1 = DateValue("9/17/2017")
Case Is = 45
     d1 = DateValue("9/24/2017")
Case Is = 46
     d1 = DateValue("10/1/2017")
Case Is = 47
     d1 = DateValue("10/8/2017")
Case Is = 48
     d1 = DateValue("10/15/2017")
Case Is = 49
End Select

If I understand what your code is supposed to be doing correctly, I think this single line of code can replace all of the code you posted...

d1 = Date - Weekday(Date) + 1

 

[chazrab]

If I understand what your code is supposed to be doing correctly, I think this single line of code can replace all of the code you posted...

d1 = Date - Weekday(Date) + 1

...and it does. Simple, elegant. d1 should = 09/24/2017 for the next 6 days and change its value to 10/1//2017 on the 7th day, and so on til EOY.
Thanks for your help, Rick.

cr