Office & Productivity Software forum

General discussion

date macro

by JSteinmetz / November 22, 2004 1:49 AM PST

I have the days of the week listed in a row, Monday, Tuesday...Through Saturday. I would like to create a macro which will put the date in for that particular day (ex Monday 11/22/2004 Tuesday 11/23/2004, etc). I am not sure if this is possible to also create it to do this no matter which day of the week the macro was run. Anyone have any hints/tips?

Discussion is locked
You are posting a reply to: date macro
The posting of advertisements, profanity, or personal attacks is prohibited. Please refer to our CNET Forums policies for details. All submitted content is subject to our Terms of Use.
Track this discussion and email me when there are updates

If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.

You are reporting the following post: date macro
This post has been flagged and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.
Sorry, there was a problem flagging this post. Please try again now or at a later time.
If you believe this post is offensive or violates the CNET Forums' Usage policies, you can report it below (this will not automatically remove the post). Once reported, our moderators will be notified and the post will be reviewed.
Collapse -
Re: date macro
by swisse / November 22, 2004 6:00 AM PST
In reply to: date macro

Put this code inside your macro:
Application.ScreenUpdating = False
Dim ctr As Integer
'if you're starting with Sunday on B1
Range("B1").Select
'this counter deletes the contents of other cells
For ctr = 1 To 7
ActiveCell.Value = vbNullString
ActiveCell.Offset(1, 0).Select
Next

Select Case Weekday(Date)
Case vbSunday
Range("B1").Select
ActiveCell.Value = Date
Case vbMonday
Range("B2").Select
ActiveCell.Value = Date
Case vbTuesday
Range("B3").Select
ActiveCell.Value = Date
Case vbWednesday
Range("B4").Select
ActiveCell.Value = Date
Case vbThursday
Range("B5").Select
ActiveCell.Value = Date
Case vbFriday
Range("B6").Select
ActiveCell.Value = Date
Case vbSaturday
Range("B7").Select
ActiveCell.Value = Date
End Select
Application.ScreenUpdating = True

You could also put it in the Workbook_Open() procedure so that it automatically puts the date in the respective column.

Hope it helps.

Swisse

Collapse -
Re: date macro
by JSteinmetz / November 22, 2004 10:04 PM PST
In reply to: Re: date macro

I tried this code on a blank sheet and it only put todays date in cell b4 I think it was.

Collapse -
Re: date macro
by Kees Bakker / November 23, 2004 12:26 AM PST
In reply to: Re: date macro

JSteinmetz,

You didn't specify how to determine the start date, given the date the macro is run. Say, today is 05/06/2006, where to start? Do you mean the current week?

So, given the input

Sunday
Monday
Tuesday
in A1:A3 (etc)

would you like to see the macro to fill B1 with 11/21/2004, B2 with 11/22/2004, B3 with 11/23/2004 etc for any day between 11/21/2004 and 11/27/2004, given that it is 11/23/2004 today? Or maybe next week?

That might be difficult. It's much simpler to fill this once yourself, and write two macros (add a week = add 7 to all cells in the range, and subtract a week = subtract 7 from all cels in the range). In fact, you can record such a macro yourself: make column of 7 7's below each other and record the copy, paste special (values, add) commands. That's all. Quite easy.


Hope this helps.


Kees

Collapse -
Re: date macro
by JSteinmetz / November 23, 2004 1:13 AM PST
In reply to: Re: date macro

Sorry about that. I will explain further. I have cells B3, D3, F3, H3, J3, L3 which are titled Monday, Tuesday, Wednesday, etc through Saturday, Sunday is not needed. If the user would run the macro on a particular Monday, say yesterday, it would be simple, =now() for Monday, =now() + 1 for Tuesday, etc but I would like it to be a little bit more user friendly if the user decided to use the macro on tuesday or another day of the week and start to fill the date on monday as the proper date. In this weeks case, 11/22/2004. I was not sure if this was possible or not.

Collapse -
Other alternative.
by Kees Bakker / November 23, 2004 4:11 AM PST
In reply to: Re: date macro

JSteinmetz,

So B3=monday.
Make D3=B3+1
Make F3=B3+2
Etc. You surely get the picture.

As soon the user fills in a date in B3, the next (5) are calculated and filled in. In Excel (as in MS Access) you only need to fill in the mm/dd-portion of the date, and the year is calculated automatically. So it's 5 characters to type in, at most (1/2 is three). That's rather user-friendly I would say.

It isn't difficult to check in B4 (or any other cell you like) if the date entered in B3 is a monday indeed. Use something like =if(weekday($b$3,1)=2,"","This is not a monday!")

If you like to program a macro and put it under a push-button, such can be done. If today is monday, make b3=now, if today is tuesday, make it now-1 etc. Possibly even, if today is sunday, make it tomorrow.

I'm sure swisse can provide the exact code, now you've made your problem clear. Personally, I think I prefer the formula's, but that's just my idea of Excel.

Hope this helps.


Kees

Collapse -
Re: Other alternative.
by JSteinmetz / November 23, 2004 4:38 AM PST
In reply to: Other alternative.

Well, this is what I ended up doing and it seems to work out pretty nice. I had a messagebox pop up and ask what Mondays date was/is. The user enters that which gets put in cell b3. the rest of the cells fill off of that. I would have rather not used the message box and made it more "invisible" to the user but this is the best it will get I guess. Thank you for your help and suggestions, i appreciate it.

Collapse -
Re: Other alternative.
by swisse / November 23, 2004 8:26 PM PST
In reply to: Re: Other alternative.

I tried to make the other procedures smaller. When you run the macro it will check what day is today. If today is Monday, it will put the date at B5, and fill in the other dates under the respective columns. If it's Wednesday it will put the current date under the column Wednesday - to be exact at F5, then it will subtract from this date and put the dates for Monday, and Tuesday. It will add to the current date to compute for the dates for Thursday, Friday, and Saturday. The problem is - if that is what JSteinmetz actually needs. So here again the revised code:

Option Base 1

Sub Button1_Click()
Application.ScreenUpdating = False
Select Case Weekday(Date)
Case vbMonday
EraseAll
CheckIfMonday
Case vbTuesday
EraseAll
CheckIfTuesday
Case vbWednesday
EraseAll
CheckIfWednesday
Case vbThursday
EraseAll
CheckIfThursday
Case vbFriday
EraseAll
CheckIfFriday
Case vbSaturday
EraseAll
CheckIfSaturday
End Select
Application.ScreenUpdating = True
End Sub

Sub EraseAll()
a = Array("B5", "D5", "F5", "H5", "J5", "L5")
For ctr = 1 To 6
Range(a(ctr)).Select
ActiveCell.Value = vbNullString
Next
End Sub

Sub CheckIfMonday()
a = Array("B5", "D5", "F5", "H5", "J5", "L5")
n = Array(0, 1, 2, 3, 4, 5)
For ctr = 1 To 6
Range(a(ctr)).Select
ActiveCell.Value = DateAdd("d", n(ctr), Date)
Next
End Sub

Sub CheckIfTuesday()
a = Array("B5", "D5", "F5", "H5", "J5", "L5")
n = Array(-1, 0, 1, 2, 3, 4)
For ctr = 1 To 6
Range(a(ctr)).Select
ActiveCell.Value = DateAdd("d", n(ctr), Date)
Next
End Sub

Sub CheckIfWednesday()
a = Array("B5", "D5", "F5", "H5", "J5", "L5")
n = Array(-2, -1, 0, 1, 2, 3)
For ctr = 1 To 6
Range(a(ctr)).Select
ActiveCell.Value = DateAdd("d", n(ctr), Date)
Next
End Sub

Sub CheckIfThursday()
a = Array("B5", "D5", "F5", "H5", "J5", "L5")
n = Array(-3, -2, -1, 0, 1, 2)
For ctr = 1 To 6
Range(a(ctr)).Select
ActiveCell.Value = DateAdd("d", n(ctr), Date)
Next
End Sub

Sub CheckIfFriday()
a = Array("B5", "D5", "F5", "H5", "J5", "L5")
n = Array(-4, -3, -2, -1, 0, 1)
For ctr = 1 To 6
Range(a(ctr)).Select
ActiveCell.Value = DateAdd("d", n(ctr), Date)
Next
End Sub

Sub CheckIfSaturday()
a = Array("B5", "D5", "F5", "H5", "J5", "L5")
n = Array(-5, -4, -3, -2, -1, 0)
For ctr = 1 To 6
Range(a(ctr)).Select
ActiveCell.Value = DateAdd("d", n(ctr), Date)
Next
End Sub

Collapse -
Hi
by avi_chitroda / August 23, 2009 2:43 PM PDT
In reply to: Re: Other alternative.

Hi

I am trying to make a similar macro but, I am stuck with the creation of the box which allows to put the date.

Could you please share it wth me

Thanks
Avi

Collapse -
Re: box.
by Kees Bakker / August 24, 2009 4:43 AM PDT
In reply to: Hi

What version of what Office Program (let me guess: MS Access 95 or MS Onenote 2003) are you trying to make the box in?

Kees

Collapse -
Re: date macro
by swisse / November 23, 2004 5:00 AM PST
In reply to: Re: date macro

This is a rather long macro with other procedures. Anyway just copy and paste them. The dates will be shown at B5, D5, F5, H5, J5, and L5. Option Base 1 should be put in the declarations area. Here is the code:

Option Base 1

Sub Button1_Click()
Application.ScreenUpdating = False
Select Case Weekday(Date)
Case vbMonday
EraseAll
CheckIfMonday
Case vbTuesday
EraseAll
CheckIfTuesday
Case vbWednesday
EraseAll
CheckIfWednesday
Case vbThursday
EraseAll
CheckIfThursday
Case vbFriday
EraseAll
CheckIfFriday
Case vbSaturday
EraseAll
CheckIfSaturday
End Select
Application.ScreenUpdating = True
End Sub

Sub EraseAll()
a = Array("B5", "D5", "F5", "H5", "J5", "L5")
For ctr = 1 To 6
Range(a(ctr)).Select
ActiveCell.Value = vbNullString
Next
End Sub

Sub CheckIfMonday()
Range("B5").Select
ActiveCell.Value = Date 'Monday
Range("D5").Select
ActiveCell.Value = DateAdd("d", 1, Date) 'Tuesday
Range("F5").Select
ActiveCell.Value = DateAdd("d", 2, Date) 'Wednesday
Range("H5").Select
ActiveCell.Value = DateAdd("d", 3, Date) 'Thursday
Range("J5").Select
ActiveCell.Value = DateAdd("d", 4, Date) 'Friday
Range("L5").Select
ActiveCell.Value = DateAdd("d", 5, Date) 'Saturday
End Sub

Sub CheckIfTuesday()
Range("B5").Select
ActiveCell.Value = DateAdd("d", -1, Date) 'Monday
Range("D5").Select
ActiveCell.Value = Date 'Tuesday
Range("F5").Select
ActiveCell.Value = DateAdd("d", 1, Date) 'Wednesday
Range("H5").Select
ActiveCell.Value = DateAdd("d", 2, Date) 'Thursday
Range("J5").Select
ActiveCell.Value = DateAdd("d", 3, Date) 'Friday
Range("L5").Select
ActiveCell.Value = DateAdd("d", 4, Date) 'Saturday
End Sub

Sub CheckIfWednesday()
Range("B5").Select
ActiveCell.Value = DateAdd("d", -2, Date) 'Monday
Range("D5").Select
ActiveCell.Value = DateAdd("d", -1, Date) 'Tuesday
Range("F5").Select
ActiveCell.Value = Date 'Wednesday
Range("H5").Select
ActiveCell.Value = DateAdd("d", 1, Date) 'Thursday
Range("J5").Select
ActiveCell.Value = DateAdd("d", 2, Date) 'Friday
Range("L5").Select
ActiveCell.Value = DateAdd("d", 3, Date) 'Saturday
End Sub

Sub CheckIfThursday()
Range("B5").Select
ActiveCell.Value = DateAdd("d", -3, Date) 'Monday
Range("D5").Select
ActiveCell.Value = DateAdd("d", -2, Date) 'Tuesday
Range("F5").Select
ActiveCell.Value = DateAdd("d", -1, Date) 'Wednesday
Range("H5").Select
ActiveCell.Value = Date 'Thursday
Range("J5").Select
ActiveCell.Value = DateAdd("d", 1, Date) 'Friday
Range("L5").Select
ActiveCell.Value = DateAdd("d", 2, Date) 'Saturday
End Sub

Sub CheckIfFriday()
Range("B5").Select
ActiveCell.Value = DateAdd("d", -4, Date) 'Monday
Range("D5").Select
ActiveCell.Value = DateAdd("d", -3, Date) 'Tuesday
Range("F5").Select
ActiveCell.Value = DateAdd("d", -2, Date) 'Wednesday
Range("H5").Select
ActiveCell.Value = DateAdd("d", -1, Date) 'Thursday
Range("J5").Select
ActiveCell.Value = Date 'Friday
Range("L5").Select
ActiveCell.Value = DateAdd("d", 1, Date) 'Saturday
End Sub

Sub CheckIfSaturday()
Range("B5").Select
ActiveCell.Value = DateAdd("d", -5, Date) 'Monday
Range("D5").Select
ActiveCell.Value = DateAdd("d", -4, Date) 'Tuesday
Range("F5").Select
ActiveCell.Value = DateAdd("d", -3, Date) 'Wednesday
Range("H5").Select
ActiveCell.Value = DateAdd("d", -2, Date) 'Thursday
Range("J5").Select
ActiveCell.Value = DateAdd("d", -1, Date) 'Friday
Range("L5").Select
ActiveCell.Value = Date 'Saturday
End Sub

Collapse -
Re: date macro
by JSteinmetz / November 24, 2004 1:27 AM PST
In reply to: Re: date macro

hmmm, I'm wondering, if I would use the =now() in a macro does excel "know" what day of the week it is? Say I use a macro with =now() in it today which is 11/24/2004, does excel realize that date coorespondes with Wednesday for today? If so, I could try to figure out that part of the macro and tie it in with this macro and the whole thing would definately be automated. I could call each different sub part depending on the day of the week.

Collapse -
Re: date macro
by JSteinmetz / November 24, 2004 2:23 AM PST
In reply to: Re: date macro

ok, so far this is what I have tested but I am getting an error because I think I have this messed up. As a test, I put in cell a1 =now() and I put the formula =text(a1,"dddd") in b1. I made the following code into a macro (obviously wrong). What am I doing wrong?


Range("B1").Select
Selection.Copy
If b1 = "Monday" Then Call CheckIfMonday
ElseIf b1 = "Tuesday" Then Call CheckIfTuesday
ElseIf b1 = "Wednesday" Then Call CheckIfWednesday
ElseIf b1 = "Thursday" Then Call CheckIfThursday
ElseIf b1 = "Friday" Then Call CheckIfFriday
ElseIf b1 = "Saturday" Then Call CheckIfSaturday
End If

Collapse -
Re: date macro
by swisse / November 24, 2004 5:21 AM PST
In reply to: Re: date macro

JSteinmetz,
You need not copy the value of A1 to B1. Just put a date in A1 and run the macro that I post below. You can check the date in A1 immediately on what day the date falls. Your formula in B1 is false, that's why it generates an error message. Better let the macro do the work. If you have to make many If's better to do it with a Case Select End Select. And lastly, I can't understand why my suggested macro won't work. So here is my revised code of your macro:

Range("A1").Select
n = ActiveCell.Value
s = WeekdayName(Weekday(n, vbMonday))
Select Case s
Case "Monday"
CheckIfMonday
Case "Tuesday"
CheckIfTuesday
Case "Wednesday"
CheckIfWednesday
Case "Thursday"
CheckIfThursday
Case "Friday"
CheckIfFriday
Case "Saturday"
CheckIfSaturday
End Select

Popular Forums

icon
Computer Newbies 10,686 discussions
icon
Computer Help 54,365 discussions
icon
Laptops 21,181 discussions
icon
Networking & Wireless 16,313 discussions
icon
Phones 17,137 discussions
icon
Security 31,287 discussions
icon
TVs & Home Theaters 22,101 discussions
icon
Windows 7 8,164 discussions
icon
Windows 10 2,657 discussions

FALL TV PREMIERES

Your favorite shows are back!

Don’t miss your dramas, sitcoms and reality shows. Find out when and where they’re airing!