Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Find out first Friday every month

1. I agreed to these rules
Join Date
25th August 2005
Posts
12

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

Excel Video Tutorials / Excel Dashboards Reports

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"))
VB:
```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 at 10:24.

3. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,478

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

4. I agreed to these rules
Join Date
25th August 2005
Posts
12

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

Excel Video Tutorials / Excel Dashboards Reports

5. I agreed to these rules
Join Date
25th August 2005
Posts
12

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

Excel Video Tutorials / Excel Dashboards Reports

6. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,478

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

7. ## Re: Find out first Friday every month

sorry, I didn't see the content of nthDayofMonth function?
Sorry, my bad. Here it is
VB:
```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

```

8. I agreed to these rules
Join Date
25th August 2005
Posts
12

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

Excel Video Tutorials / Excel Dashboards Reports

9. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,478

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

10. I agreed to these rules
Join Date
25th August 2005
Posts
12

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

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