Function to add a week

swoolsey

New Member
Joined
Oct 15, 2002
Messages
7
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
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

maxflia10

Well-known Member
Joined
May 20, 2002
Messages
890
If you already have entered a column or row of dates i.e. 10/18/02 manually you could use

=text(a1,"dddd")
 

swoolsey

New Member
Joined
Oct 15, 2002
Messages
7
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?
 

swoolsey

New Member
Joined
Oct 15, 2002
Messages
7

ADVERTISEMENT

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.
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454

ADVERTISEMENT

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

Well-known Member
Joined
Feb 16, 2002
Messages
3,215
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

New Member
Joined
Oct 15, 2002
Messages
7
Thank you both for the replies. Being a sequential thinker, I attemtped Yogi's suggestion first and it worked perfectly!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,536
Messages
5,596,744
Members
414,094
Latest member
dingo_baby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top