Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Identify first & last Tuesday in Month

1. Member
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. ## 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. Member
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. ## 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. Member
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

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

#### 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