Find out first Friday every month

[noiseash]

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!

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

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 

[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

[noiseash]

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

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

[noiseash]

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

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

[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

[Dave Hawley]

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

Sorry, my bad. Here it is

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 

[noiseash]

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!

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

[Krishnakumar]

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

[noiseash]

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.