Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Find out first Friday every month

  1. #1
    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. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,718

    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 11:24.

  3. #3
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,383

    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. #4
    Join Date
    25th August 2005
    Posts
    12

    Re: Find out first Friday every month

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

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

    Quote 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. #6
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,383

    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. #7
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,718

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

    Quote 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. #9
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,383

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


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Find The Friday Date Of The Month
    By pradeep_atm in forum Excel and/or Word Help
    Replies: 2
    Last Post: February 29th, 2008, 05:55
  2. Return Last Friday From A Given Month
    By Smarty_Great in forum EXCEL HELP
    Replies: 8
    Last Post: May 23rd, 2007, 23:05
  3. Find a certain month in a column of dates
    By the_crooked_toe in forum EXCEL HELP
    Replies: 7
    Last Post: July 15th, 2006, 02:24
  4. VBA Code to find fiscal Month
    By Justice23 in forum EXCEL HELP
    Replies: 3
    Last Post: April 29th, 2006, 00:29
  5. Return last Friday from a given month
    By bvan in forum EXCEL HELP
    Replies: 7
    Last Post: November 23rd, 2004, 17:23

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