I need a formula in VBA to update the date in excel on a weekly basis. New to vb, and not much luck trying to research it. any suggestions would be great thanks!

re: "update the date in excel on a weekly basis" Excel is an application. It uses the system date. You can not "update the date in excel" without changing the system date.

Do you mean that you have a date in a specific cell in a specific sheet in a specific workbook that you want updated when the workbook is opened?

If so, please provide a little more detail, such as where the date is located (we don't need the file name) and what you mean by "updated on a weekly basis".

e.g. Should it always have athe latest "Monday date" in the cell or "today's date" if the date is over 7 days old, etc.

The more we understand about your goal, the better a solution we can suggest.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

thank you for your reply. I know there is a formula in excel that updates the date daily to today's date. I want something that does the same thing, only weekly. For instance i am trying to create a weekly time and attendace chart in excel that tracks the work hours and tasks for certain employees. instead of having to change the monday-friday dates to the current week dates every week, i want a formula that will do this automatically. Monday will be in a cell in the worksheet, then tuesday in a different cell, then wednesday, etc. I believe there is no formula in excel to do this, but that i may be able to do it in VB.

I think this will work for you without VBA. You can change your system's date to test it. You'll need to force a calculation after you change the date to get it to update. Somewhere in your workbook put a list of all the Monday dates. I'll use G1:G6 as an example. You can hide this column if you want.

G 1 Mondays 2 07/16/2012 3 07/23/2012 4 07/30/2012 5 08/06/2012 6 08/13/2012Then use these formulas to fill in your dates, which should update automatically each week:

A B C 1 MONDAY TUESDAY WEDNESDAY 2 =VLOOKUP(TODAY(),G2:G10,1,1) =A2+1 =B2+1Note that the VLOOKUP uses the

approximate matchargument of "1" (TRUE) to return "the largest value that is less than or equal to lookup_value"TODAY()which means that it will always return the current week's Monday date".

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

that works! thank you so much :D :D i appreciate it.

Ask Your Question

Weekly Poll