Link to home
Start Free TrialLog in
Avatar of cwbarrett
cwbarrett

asked on

Create table with 1 year of dates 1 week apart

Hi,

With a given base date say of 1/1/2014, I would like to create a record set of 52 records incrementing the date by 1 week in each record so it is the same day-of-week in each date (taking Leap year into account).  Each record also needs to be consecutively numbered beginning with 1 on the base date and 52 on the last.

Macro's are nice but I think this needs to be VBA.  

Help is appreciated.
Charlie
SOLUTION
Avatar of Graham Mandeno
Graham Mandeno
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You can also use a tally/number table for this, as I show in this article:
https://www.experts-exchange.com/A_5410.html

I showed the example of sequential dates in the article.  You can multiply the number table values by 7 to get the week intervals you seek.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of cwbarrett
cwbarrett

ASKER

Thank you both for responding, however, its' going to be a week or 2 before I can test the solutions.  I will get back to this.
No problem.

/gustav
Thank you both for the solution.  One seemed easier but didn't account for leap year.  I think I can come up with a solution that uses both.

Thank you!
Charlie
You are welcome!

You could use DateAdd with the db.Execute method to account for leap years, but why would you? DAO is much faster here and does it all.

/gustav
You're saying this will execute faster?
Yes. 52 calls of db.execute is not very fast.

But why don't you try? It shouldn't take you more that a few minutes ...

/gustav