Announcement

Collapse
No announcement yet.

Identify first & last Tuesday in Month

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

  • Identify first & last Tuesday in Month



    Hi All,

    A brain teaser I am struggling with. How do you identify the first Tuesday and Last Tuesday in any given month in any given year??

    I am building a report which has a number of columns depicting results for a weekending Tuesday. Depending on the month and year, some reports will require four columns and some five. I need to be able to determine how many columns for the given month, then drop the weekending Tuesday date into the header for each column.

    Any ideas on the simplest way to determine whether a month has 4 or 5 Tuesdays and what dates they fall on?

    Cheers
    Tim

  • #2
    Re: Identify first & last Tuesday in Month

    Hi timc,

    There was a thread here last week entitled "return last friday from a given month".

    You should look that up and modify the formulas accordingly, I have copied and pasted some of it.
    This will calculate the last Friday of the month in which the date in A1 falls:

    =DATE(YEAR(A1),MONTH(A1)+1,0)+MOD(-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)-2,-7)

    HTH
    The final post in the thread has an attachment explaining how the formula works.

    Justin

    Comment


    • #3
      Re: Identify first & last Tuesday in Month

      Hi Justin,

      Brilliant - thanks for the trouble to reply.

      Cheers
      Tim

      Comment


      • #4
        Re: Identify first & last Tuesday in Month

        Hi Tim

        I have 2 Custom Function that will do this. To use them push Alt+F11 then go to Insert>Module and in here paste the codes below;
        Code:
        Function HowManyDaysInMonth(FullDate As String, sDay As String) As Integer
        Dim i As Integer
        Dim iDay As Integer, iMatchDay As Integer
        Dim iDaysInMonth As Integer
        Dim FullDateNew As Date
        
        iMatchDay = Weekday(FullDate)
            Select Case UCase(sDay)
               Case "SUN"
                iDay = 1
               Case "MON"
                iDay = 2
               Case "TUE"
                iDay = 3
               Case "WED"
                iDay = 4
               Case "THU"
                iDay = 5
               Case "FRI"
                iDay = 6
               Case "SAT"
                iDay = 7
            End Select
          iDaysInMonth = Day(DateAdd("d", -1, DateSerial _
                    (Year(FullDate), Month(FullDate) + 1, 1)))
          FullDateNew = DateSerial(Year(FullDate), Month(FullDate), iDaysInMonth)
                For i = iDaysInMonth - 1 To 0 Step -1
                    If Weekday(FullDateNew - i) = iDay Then
                        HowManyDaysInMonth = HowManyDaysInMonth + 1
                    End If
                Next i
        End Function
        
        Function DatesDayOccur(FullDate As String, sDay As String) As String
        Dim i As Integer
        Dim iDay As Integer, iMatchDay As Integer
        Dim iDaysInMonth As Integer
        Dim FullDateNew As Date
        Dim strDates As String
        
        iMatchDay = Weekday(FullDate)
            Select Case UCase(sDay)
               Case "SUN"
                iDay = 1
               Case "MON"
                iDay = 2
               Case "TUE"
                iDay = 3
               Case "WED"
                iDay = 4
               Case "THU"
                iDay = 5
               Case "FRI"
                iDay = 6
               Case "SAT"
                iDay = 7
            End Select
          iDaysInMonth = Day(DateAdd("d", -1, DateSerial _
                    (Year(FullDate), Month(FullDate) + 1, 1)))
          FullDateNew = DateSerial(Year(FullDate), Month(FullDate), iDaysInMonth)
                For i = iDaysInMonth - 1 To 0 Step -1
                    If Weekday(FullDateNew - i) = iDay Then
                        strDates = FullDateNew - i & "," & strDates
                    End If
                Next i
            DatesDayOccur = Left(strDates, Len(strDates) - 1)
        End Function
        Now click the top right X to get back to Excel proper and Save.

        Now in any cell use in any of the ways shown below;

        =HowManyDaysInMonth(A1,"tue") Where A1 has the date
        OR
        =HowManyDaysInMonth("1/1/04","tue")


        =DatesDayOccur(A1,"Tue") Where A1 has the date
        OR
        =DatesDayOccur("1/1/04","Tue")

        Comment


        • #5


          Re: Identify first & last Tuesday in Month

          Dave,

          Uncanny. Exactly what I needed. Thankyou for the generosity of your time and expertise.

          Cheers
          Tim

          Comment

          Working...
          X