Issue with Week# to Date functiion after the new year

[mnadamn19]

I am using the following code to generate the last date of a week based on the week number. Everything has worked well until the new year hit and now the dates are off by a week (week 1 shows a date of 12/31/2016 instead of 1/7/17). This code has worked well in the past (has not had to deal with new year's). Could this be because January 1st was on Sunday? Is there anything I can do to make this function for years to come? I have been trying to get this working for a while and any help would be greatly appreciated.

Please Login or Register  to view this content.

[zbor]

You don't need VBA.
This would work...

Please Login or Register  to view this content.

Writed in VBA it can be:

Please Login or Register  to view this content.

[Richard Buttrey]

Hi,

I'd simplify it to

Please Login or Register  to view this content.

Or as just a formula in a cell

Please Login or Register  to view this content.

Where A1 contains the week number

[zbor]

Yea, I'm missing last part.

[mnadamn19]

Both answers have Jan. 1st as the first day of the week no matter which day it falls on. This means that no matter the year, the end of week 1 always gets reported as 1/7/xxxx I need the week to start on Sunday. I have found that the issue in my original post happens if the first of the year takes place on Sunday - Wednesday and does not happen the rest of the week. I have tried removing vbfirstfourdays from my code and this issue is still occuring. I may just have to do an if statement looking at the value produced by the weekday function.

[mnadamn19]

This seems to have taken care of it. If anyone knows of a more elegant solution (or why it didn't work originally), please let me know as I am still learning. Thanks!

Please Login or Register  to view this content.

[Richard Buttrey]

Both answers have Jan. 1st as the first day of the week no matter which day it falls on. This means that no matter the year, the end of week 1 always gets reported as 1/7/xxxx I need the week to start on Sunday. I have found that the issue in my original post happens if the first of the year takes place on Sunday - Wednesday and does not happen the rest of the week. I have tried removing vbfirstfourdays from my code and this issue is still occuring. I may just have to do an if statement looking at the value produced by the weekday function.

That's not correct.
When the Year is 2017 the formula in #3 returns Saturday 7 January for week1 which is what you want.
Next year the same formula will return Saturday 6 January 2018. You can easily demonstrate this by temporarily adding +365 to the year part of the formula to force it to be 2018. i.e.

Please Login or Register  to view this content.

[mnadamn19]

Sorry about that, I see what i did wrong. I only replaced the first year(date) with a year not the second as well. It does work in the formula form but I am getting a type mismatch with the function. As I have figured out how to get my code to work, I am just going to stick with that. Thanks