Announcement

Collapse
No announcement yet.

code to return a first wednesday of a month

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • code to return a first wednesday of a month



    hi guys

    can any1 help me out with finding a code to check if todays is the first wednesday (or any other day) of the month?

    ps. month is not specified/inputed, so it has to figure out from the current date that if today is wednesday and if it is the first wednesday!

    thanks

  • #2
    Re: code to return a first wednesday of a month

    something like this UDF would work

    Code:
    Function FirstWhatDay(StartDate As Date, MyWeekday As Integer)
    'Will return the date of the first occurance of the Weekday specified
    'after the Start Date
    
    Do Until Weekday(StartDate, vbMonday) = MyWeekday
        StartDate = StartDate + 1
    Loop
    FirstWhatDay = StartDate
    
    End Function
    Last edited by Will Riley; August 25th, 2005, 20:21.
    Kind Regards, Will Riley

    LinkedIn: Will Riley

    Comment


    • #3
      Re: code to return a first wednesday of a month

      Hi,

      Try,

      =(DATE(YEAR(TODAY()),MONTH(TODAY()),1)-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1),3))+2

      will return the first Wednesday of the month

      HTH
      Kris

      ExcelFox

      Comment


      • #4
        Re: code to return a first wednesday of a month

        Small correction in the formula. Here is a revised one.

        Define SDATE

        =DATE(YEAR(TODAY()),MONTH(TODAY()),1)

        To return first Wednesday

        =IF(MONTH((SDATE-WEEKDAY(SDATE,1))+4)<>MONTH(SDATE),((SDATE+7-WEEKDAY(SDATE+7,1))+4),(SDATE-WEEKDAY(SDATE,1))+4)

        HTH
        Kris

        ExcelFox

        Comment


        • #5


          Re: code to return a first wednesday of a month

          The formula will test today's date to see if it is the First Wednesday. Replace the text strings with whatever you want, including two double-quotation marks for a blank cell.

          If you want to test for a different day of the week, change the 4 to some other number... Sunday = 1, Monday = 2, etc.

          Code:
          =IF(WEEKDAY(TODAY(),1)=4,IF(DAY(TODAY())<=7,"First Wednesday","Wednesday, But Not First"),"Not Wednesday")
          Alex.

          Comment

          Working...
          X