Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Identify first & last Tuesday in Month

  1. #1
    Join Date
    19th November 2004
    Posts
    20

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    6th September 2004
    Location
    Australia
    Posts
    580

    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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    19th November 2004
    Posts
    20

    Re: Identify first & last Tuesday in Month

    Hi Justin,

    Brilliant - thanks for the trouble to reply.

    Cheers
    Tim

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,697

    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;
    VB:
    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")

  5. #5
    Join Date
    19th November 2004
    Posts
    20

    Re: Identify first & last Tuesday in Month

    Dave,

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

    Cheers
    Tim

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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