will this date function work?

[cspritke]

Hi,
I finally got a function together that I THINK will work, but I'm not sure how to test it. The idea is that I need my macro to set cell A1 to hold the first day of next Monday's month. If today is Monday, then it should use today, NOT next monday.
a few examples:
Running the macro today gives 8/1/2002
Running it on 8/25/2002 gives 8/1/2002
Running it on 8/26/2002 gives 8/1/2002
Running it on 8/27/2002 gives 9/1/2002
Running it on 9/01/2002 gives 9/1/2002

I don't know how to test my code for running on any day other than today because it makes use of the "Today()" function and I can't get it to work using a variable instead.

Here's my code:

Dim dayOfWeek
Dim nextMonday
Dim aMonday
Dim numDays
numDays = 7
aMonday = 2
dayOfWeek = Weekday(Now)
nextMonday = (numDays + aMonday - dayOfWeek) Mod numDays

Range("A1").Formula = "=DATE(YEAR(Today()+" & nextMonday & "),MONTH(Today()+" & nextMonday & "),1)"

I'd appreciate any comments on whether it will work and how to test it!

Thanks in advance
Christine

 

[cspritke]

I just realized that my code can't use the Today() function anyway because I don't want that date in A1 to ever change, but with my code it will change over time. Any suggestions??? PLEASE HELP!

Thanks,
Christine

 

[Corni]

I would suggest following code:

Dim aMonday
Dim numDays
Dim testDate
numDays = 7
aMonday = 2
testDate = InputBox("Enter the date of test (eg. 6/28/02): ")
Range("A1").Formula = "=IF(OR(WEEKDAY(""" & testDate & """)=1,WEEKDAY(""" & testDate & """)=2),DATE(YEAR(""" & _
testDate & """),MONTH(""" & testDate & """),1),IF(AND(""" & testDate & """+" & numDays + aMonday & "-WEEKDAY(""" & testDate & _
""")>EOMONTH(""" & testDate & """,0),MONTH(""" & testDate & """)<>12),DATE(YEAR(""" & testDate & """),MONTH(""" & _
testDate & """)+1,1),IF(AND(""" & testDate & """+" & numDays + aMonday & "-WEEKDAY(""" & testDate & _
""")>EOMONTH(""" & testDate & """,0),MONTH(""" & testDate & """)=12),DATE(YEAR(""" & testDate & """),MONTH(""" & _
testDate & """)+1,1),DATE(YEAR(""" & testDate & """),MONTH(""" & testDate & """),1))))"

It seems a little bit complicated but it takes into account all possible situations during a calendar year. Good luck.

 

[Nickleby]

Here's an alternative macro.

[A1].Formula = "=IF(WEEKDAY(TODAY())=2,TODAY()-DAY(TODAY())+1,TODAY()+7-MOD(TODAY()-2,7)-DAY(TODAY()+7-MOD(TODAY()-2,7))+1)"
[A1] = [A1].Value

If you want to test this macro, enter some dates in column B, in the formula per the macro change all the TODAY() to B1 (using find/replace), run the first line only of the macro (to enter the formula in A1), fill down the formula on the worksheet.
The same can be done to test your macro.

 

[Tommy Bak]

Here is another alternative.
this will place the date in A1 and will only change if you run the the macro.

Sub insertdate1()
[a1] = DateSerial(Year(Date), Month((Date) + (8 - Weekday(Date, vbMonday))), 1)
End Sub

regards tommy

 

[cspritke]

Great! I got it working. Thank you all so much

Christine