Solved Formula for weekly update in VBA

July 16, 2012 at 09:20:47
Specs: Windows XP
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!

See More: Formula for weekly update in VBA

Report •

July 16, 2012 at 10:30:33
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.

Report •

July 16, 2012 at 10:44:01
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.

Report •

July 16, 2012 at 11:14:23
✔ Best Answer
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.

1      Mondays
2     07/16/2012
3     07/23/2012
4     07/30/2012
5     08/06/2012
6     08/13/2012

Then 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+1

Note that the VLOOKUP uses the approximate match argument 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.

Report •

Related Solutions

July 16, 2012 at 12:23:18
that works! thank you so much :D :D i appreciate it.

Report •

Ask Question