Using a Macro to add Days to a date

[icewiper]

I have an excel spreadsheet that creates eamployees timesheets. Mon thru Sun have dates in them. They do not run off of current date. I am trying to set up a macro that on a click of a button it adds 7 days to each date.
I named each date field as the day of the week.
Here is what I have attenped to do.

Dim Mon, Tue, Wed, Thur, Fri, Sat, Sun As Date
Const x As Integer = 7

Mon = DateAdd("d", 7, Mon)
Tue = DateAdd("d", 7, Tue)
and so forth

I do not recieve any errors but nothing happens
any ideas?

 

[Zathras]

Excel is not the same as SQL Server.

All you have to do is add 7.

 

[TonyJollans]

Hi icewiper,

You should be aware that

[purple]Dim Mon, Tue, etc, Sun As Date[/purple]

does not define a lot of Date variables. It defines Sun as a Date, and Mon, Tue, etc. as Variants.

However you are not showing any initialisation of your variables. So where are they set up, and where do you want the results to be? If you are using and/or want your dates on a worksheet you must work with the worksheet somewhere - and probably don't need the variables at all.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[icewiper]

I've tried the add 7 right at the begining but had the same results.
Unless I am adding it differently.
Could you give an example please?

Thank you

 

[pbrodsky]

I have a feeling that the problem lies in the original values of the variables: Mon, Tue, ...

You mention "I have an excel spreadsheet that creates eamployees timesheets. Mon thru Sun have dates in them."

You don't give any indication of where your VBA is getting its values, or how they relate back to the spreadsheet. Are you, perhaps, trying to refer to "named ranges" on the spreadsheet - the syntax for that is completely different. Please post more details so people can give you better advice.

 

[SkipVought]

FYI: Check out

Why do Dates and Times seem to be so much trouble? faq68-5827

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[Zathras]

I've tried the add 7 right at the begining but had the same results.

You didn't actually say what the results are, but I would guess that your "dates" are really strings (text).

Can you provide any more details of exactly what you are doing?

 

[Bogiemon]

Wouldn't it be easier to have the Monday Date in say Cell A1. Then the formula in B1 =A1 + 1, C1 = B1 + 1 etc...

Then all you would need to do is add 7 to Cell A1.

Range("A1").Value = Range("A1").Value + 7