Function to add a week

[swoolsey]

I am using Excel to create a work scheduler that I want to automate as much as possible. I have a row of dates representing each day of the week with the current dates. So it would be a row with Monday through Sunday, each in its own column with the corresponding date in the row beneath, in mm/dd/yyyy format. I already have set Tuesday's date =B7+1, Wed date =B7+2...through to Sunday so that I only need to change the date for Monday and the rest of the dates change automatically.

I want create function to automate the process of going to the next week and assign that function to a button userform. The button would add 7 days to Mondays date;it would change 10/18/2002 to 10/25/2002.

Thanks in advance for any reply.

This is a great forum

Steve

 

[maxflia10]

If you already have entered a column or row of dates i.e. 10/18/02 manually you could use

=text(a1,"dddd")

 

[swoolsey]

Thanks for the reply.

How would I assign this as a macro to a button userform to automate setting Mondays date to Monday of next week?

 

[maxflia10]

Swoosley,

I'm inept at best with formulas and plain ignorant in VBA.

 

[swoolsey]

Thanks for you help anyway. It just great to have a forum like this to have someone else to bounce off of. My feeling is that what I am trying to do is probably easy with a simple function assigned to a button. I just need some guidance from the gurus out there.

 

[maxflia10]

Just "bumping" this to the top, hoping someone can help Swoosley

 

[Yogi Anand]

Hi Steve:

I understand that your starting date is set in cell B7; and now you would like to create a BUTTON that when you click the BUTTON the date in cell B7 will advance by 7 ... if it is so, then

1. Create a Button on the worksheet
2. assign the code [B7]=[B7]+7 to Button1_Click action of the code

then if your starting date was 10/18/02, clicking on the button will advance it to 10/25/02

Regards!

Yogi
This message was edited by Yogi Anand on 2002-10-18 19:27

 

[Jack in the UK]

This might help i have written UDF for you add the the sheet module might be best

First is long had allow date range and add as many days as you like in the formula pop up

Second will automatically add 7 days as you request

These will not sink in the range cell its self and will need to be in its own column or row

Function Jacks_Days_To_Add(Rng As Range, Days_To_Add As Integer) As Variant

Application.Volatile

Jacks_Days_To_Add = Format(Rng.Value + Days_To_Add, "dddd d mmmm yyyy")
' Formula is assumes A1 is entered as =NOW()
' =Jacks_Days_To_Add(A1,7)

End Function


Function Jacks_Add_7_Days(Rng As Range) As Variant

Application.Volatile

Jacks_Add_7_Days = Format(Rng.Value + 7, "dddd d mmmm yyyy")

' Formula is assumes A1 is entered as =NOW()
' =Jacks_Add_7_Days(A1)
End Function

 

[swoolsey]

Thank you both for the replies. Being a sequential thinker, I attemtped Yogi's suggestion first and it worked perfectly!