Announcement

Collapse
No announcement yet.

Find out first Friday every month

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

  • Find out first Friday every month

    I have problem with writing the formula that copy the content of one sheet to another first Friday every month, some second Friday every month, third, fourth....etc.

    What I want is, eg, in cell "A1" of sheet2
    if (today=first Friday, sheet1!A1,"")

    i don't know how to let the system judge if today is a first Friday of this month or not. I write a formula like this:

    if(weekday(today())=5,sheet1!A1,"")
    But this only works for every Friday, not for First/Second...Fridays

    Any help will be appreciated. Thank you!

  • #2
    Re: Find out first Friday every month

    Hi noiseash

    Try this custom function. It will return a number between 0 and 4 based on the day and date you specify.

    To use the custum function, Go to Tools>Macro>Visual Basic Editor(Alt+F11) and go to Insert>Module and paste in the code below. Then come back to Excel proper and use it like;
    =nthDayOfMonth("Fri",TODAY())

    You can nest it with an IF Function and the CHOOSE function to tell excel what action it should take. E.g;
    =IF(nthDayOfMonth("Thu",TODAY())=0,"",CHOOSE(nthDayOfMonth("THU",TODAY()),"First","Second","Third","Fourth"))
    Code:
    Function nthDayOfMonth(sWeekDay As String, dDate As Date)
    Dim lDay As Long
    Dim lDayCount As Long
    Dim lWeekDay As Long
    
    
        Select Case UCase(sWeekDay)
           Case Is = "MON"
             lWeekDay = 2
           Case Is = "TUE"
             lWeekDay = 3
           Case Is = "WED"
             lWeekDay = 4
           Case Is = "THU"
             lWeekDay = 5
           Case Is = "FRI"
             lWeekDay = 6
           Case Is = "SAT"
             lWeekDay = 7
           Case Is = "SUN"
             lWeekDay = 1
        End Select
    
        On Error Resume Next
        For lDay = 1 To Day(dDate)
            If WeekDay(DateSerial(Year(dDate), Month(dDate), lDay)) = lWeekDay Then
                lDayCount = lDayCount + 1
            End If
        Next lDay
        
        nthDayOfMonth = lDayCount
    
    End Function
    Last edited by Dave Hawley; September 2nd, 2005, 10:24.

    Comment


    • #3
      Re: Find out first Friday every month

      Hi,

      Try,

      Define SDATE,

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

      To return first Friday,

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

      for second Friday,

      replace 6 with 13 in the above formula.

      HTH
      Kris

      ExcelFox

      Comment


      • #4
        Re: Find out first Friday every month

        sorry, I didn't see the content of nthDayofMonth function?

        Originally posted by Dave Hawley
        Hi noiseash

        Try this custom function. It will return a number between 0 and 4 based on the day and date you specify.

        To use the custum function, Go to Tools>Macro>Visual Basic Editor(Alt+F11) and go to Insert>Module and paste in the code below. Then come back to Excel proper and use it like;
        =nthDayOfMonth("Fri",TODAY())

        You can nest it with an IF Function and the CHOOSE function to tell excel what action it should take. E.g;
        =IF(nthDayOfMonth("Thu",TODAY())=0,"",CHOOSE(nthDayOfMonth("THU",TODAY()),"First","Second","Third","Fourth"))

        Comment


        • #5
          Re: Find out first Friday every month

          Yes, this one works fine to return the first Friday date of the month. but this is not exactlly what I want.

          I want the system judge if TODAY is the first Friday of the month and then action if the condition is true

          I think you are not far from there? I don't really understand how your formula works

          Thanks

          Originally posted by Krishnakumar
          Hi,

          Try,

          Define SDATE,

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

          To return first Friday,

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

          for second Friday,

          replace 6 with 13 in the above formula.

          HTH

          Comment


          • #6
            Re: Find out first Friday every month

            Hi,

            Try this,

            =IF(TODAY()=IF(MONTH((SDATE-WEEKDAY(SDATE,1))+6)<>MONTH(SDATE),((SDATE+7-WEEKDAY(SDATE+7,1))+6),(SDATE-WEEKDAY(SDATE,1))+6),Sheet1!A1,"")

            HTH
            Kris

            ExcelFox

            Comment


            • #7
              Re: Find out first Friday every month

              sorry, I didn't see the content of nthDayofMonth function?
              Sorry, my bad. Here it is
              Code:
              Function nthDayOfMonth(sWeekDay As String, dDate As Date)
              Dim lDay As Long
              Dim lDayCount As Long
              Dim lWeekDay As Long
              
              
                  Select Case UCase(sWeekDay)
                     Case Is = "MON"
                       lWeekDay = 2
                     Case Is = "TUE"
                       lWeekDay = 3
                     Case Is = "WED"
                       lWeekDay = 4
                     Case Is = "THU"
                       lWeekDay = 5
                     Case Is = "FRI"
                       lWeekDay = 6
                     Case Is = "SAT"
                       lWeekDay = 7
                     Case Is = "SUN"
                       lWeekDay = 1
                  End Select
              
                  On Error Resume Next
                  For lDay = 1 To Day(dDate)
                      If WeekDay(DateSerial(Year(dDate), Month(dDate), lDay)) = lWeekDay Then
                          lDayCount = lDayCount + 1
                      End If
                  Next lDay
                  
                  nthDayOfMonth = lDayCount
              
              End Function

              Comment


              • #8
                Re: Find out first Friday every month

                Your formula works like a magic!!!
                took me a while to figure it out how it works!
                one question:
                is it only when the first day of the month is a Saturday, the following
                MONTH((SDATE-WEEKDAY(SDATE,1))+6)<>MONTH(SDATE)
                will be true?

                take this year as example, Octorber is the only month which meets this condition!

                Thank you sooooo much!

                Originally posted by Krishnakumar
                Hi,

                Try this,

                =IF(TODAY()=IF(MONTH((SDATE-WEEKDAY(SDATE,1))+6)<>MONTH(SDATE),((SDATE+7-WEEKDAY(SDATE+7,1))+6),(SDATE-WEEKDAY(SDATE,1))+6),Sheet1!A1,"")

                HTH

                Comment


                • #9
                  Re: Find out first Friday every month

                  Hi,

                  I included this formula MONTH((SDATE-WEEKDAY(SDATE,1))+6)<>MONTH(SDATE), because for some days the first day would falls in the previous month.

                  For eg. if we take Wednesday,

                  =(SDATE-WEEKDAY(SDATE,1))+4 will return 8/31/05 whereas,

                  =IF(MONTH((SDATE-WEEKDAY(SDATE,1))+4)<>MONTH(SDATE),((SDATE+7-WEEKDAY(SDATE+7,1))+4),(SDATE-WEEKDAY(SDATE,1))+4) will return 9/7/05

                  HTH
                  Kris

                  ExcelFox

                  Comment


                  • #10
                    Re: Find out first Friday every month

                    yes. but is it the only case that:
                    the day fall in last month in your case because the first day of September 2005 is a Thursday, which is one day after Wednesday.

                    so if we're looking for Friday in my case, only Janurary and Octorber 2005 will fall to a day last month, because the first day of the two months are Saturday, again, one day behind

                    This is just based on my observation, don't know if it is correct. i'm really bad at calenders, never able to calculate dates, weekdays, etc.

                    Comment


                    • #11
                      Re: Find out first Friday every month

                      Hi noiseash

                      see this formula...it will return the first friday of the month of a particular date..maybe this can be of some help to you

                      Code:
                      =IF(WEEKDAY(EOMONTH(A2,-1)+1,2)=5,EOMONTH(A2,-1)+1,IF(WEEKDAY(EOMONTH(A2,-1)+1,2)<5,EOMONTH(A2,-1)+6-WEEKDAY(EOMONTH(A2,-1)+1,2),IF(WEEKDAY(EOMONTH(A2,-1)+1,2)>5,EOMONTH(A2,-1)+12-WEEKDAY(EOMONTH(A2,-1),2),0)))
                      Here the date is in Cell A2

                      HTH

                      pangolin

                      Comment

                      Working...
                      X