Results 1 to 6 of 6

will this date function work?

This is a discussion on will this date function work? within the Excel Questions forums, part of the Question Forums category; Hi, I finally got a function together that I THINK will work, but I'm not sure how to test it. ...

  1. #1
    New Member
    Join Date
    Jun 2002
    Location
    California
    Posts
    12

    Default

    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

  2. #2
    New Member
    Join Date
    Jun 2002
    Location
    California
    Posts
    12

    Default

    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

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Brampton
    Posts
    325

    Default

    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.

  4. #4

    Join Date
    May 2002
    Posts
    26

    Default

    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.

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Guderup, Denmark
    Posts
    287

    Default

    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

  6. #6
    New Member
    Join Date
    Jun 2002
    Location
    California
    Posts
    12

    Default

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

    Christine

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com