Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: MS Acces vba function to get thursday as first day of week in current month and Wednesday as last week in current month.

  1. #1
    New Member
    Join Date
    Oct 2018
    Location
    Hyderabad, Sindh, Pakistan
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default MS Acces vba function to get thursday as first day of week in current month and Wednesday as last week in current month.

    Function for get Thursday as first day of week in current week month, this function returning first week as Thursday but first week of month in last month, But K need it will return only current month. For example if 1st date of month is on Tuesday it will return 1st date of month.


    Function GetFirstofWeek(dtDate) As Date

    '=MAX(C3-WEEKDAY(C3,14)+1, EOMONTH(C3, -1)+1)
    GetFirstofWeek = DateAdd("d", dtDate, -Weekday(dtDate, vbThursday) + 1)
    End Function
    Function for get Wednesday as last day of week in current week month, this function returning last week as Wednesday but last week of month in next month, But I need it will return only current month. For example if last date of month is on Monday it will return 30th date of current month.
    Function GetLastofWeek(dtDate As Date)
    '=MIN(B2+(7-WEEKDAY(B2,14)), EOMONTH(B2, 0))
    GetLastofWeek = DateAdd("d", dtDate, (7 - (Weekday(dtDate, vbWednesday)) + 1))
    End Function
    This function will return last date of month
    Public Function EOMonth(RefDt As Date, Optional Months As Integer = 0) As Date
    On Error GoTo Error_Handler
    'DateSerial(Year([TxtDate]),Month([TxtDate])+1,0)

    EOMonth = DateSerial(Year(RefDt), Month(RefDt) + Months + 1, 0)
    End Function

    This function will return first date of month
    Public Function SOMonth(RefDate As Date, Optional Months As Integer = 0) As Date
    On Error GoTo Error_Handler
    'DateSerial(Year([TxtDate]),Month([TxtDate])-0,0)+1
    SOMonth = DateSerial(Year(RefDate), Month(RefDate) + Months - 0, 0) + 1

  2. #2
    Board Regular
    Join Date
    May 2013
    Posts
    424
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MS Acces vba function to get thursday as first day of week in current month and Wednesday as last week in current month.

    The WEEKDAY function already has a parameter to specify the first day of a week?

    https://www.excelfunctions.net/vba-w...-function.html
    Excel 2007

  3. #3
    New Member
    Join Date
    Oct 2018
    Location
    Hyderabad, Sindh, Pakistan
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MS Acces vba function to get thursday as first day of week in current month and Wednesday as last week in current month.

    Quote Originally Posted by welshgasman View Post
    The WEEKDAY function already has a parameter to specify the first day of a week?

    https://www.excelfunctions.net/vba-w...-function.html
    But it retuns last month first day of week as thursday.

    And also in last day of week it returns last day as wednesday in next month.

  4. #4
    Board Regular
    Join Date
    May 2013
    Posts
    424
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MS Acces vba function to get thursday as first day of week in current month and Wednesday as last week in current month.

    What I was suggesting was using that parameter, could cut down on all the math you have you do if the first day is Sunday/Monday?
    Excel 2007

  5. #5
    New Member
    Join Date
    Oct 2018
    Location
    Hyderabad, Sindh, Pakistan
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MS Acces vba function to get thursday as first day of week in current month and Wednesday as last week in current month.

    Quote Originally Posted by welshgasman View Post
    What I was suggesting was using that parameter, could cut down on all the math you have you do if the first day is Sunday/Monday?
    I have tried it,but not work. Can I send you ms access database.

  6. #6
    Board Regular
    Join Date
    May 2013
    Posts
    424
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MS Acces vba function to get thursday as first day of week in current month and Wednesday as last week in current month.

    No, I am not sure even what you want from your description.?
    I *think* you are looking for the first Thursday in the current month, and the last Wednesday in the current month?
    If so I would just get the weekday of the first and last days of current month and then add subtract accordingly the number of days.?
    Weekday of first of month
    Code:
    weekday(dateserial(Year(date),month(date),0)+1)
    Weekday of last of month
    Code:
    weekday(dateserial(Year(date),month(date)+1,0)
    then do the math for your Thursday or Wednesday.

    HTH
    Excel 2007

  7. #7
    New Member
    Join Date
    Oct 2018
    Location
    Hyderabad, Sindh, Pakistan
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MS Acces vba function to get thursday as first day of week in current month and Wednesday as last week in current month.

    Quote Originally Posted by welshgasman View Post
    No, I am not sure even what you want from your description.?
    I *think* you are looking for the first Thursday in the current month, and the last Wednesday in the current month?
    If so I would just get the weekday of the first and last days of current month and then add subtract accordingly the number of days.?
    Weekday of first of month
    Code:
    weekday(dateserial(Year(date),month(date),0)+1)
    Weekday of last of month
    Code:
    weekday(dateserial(Year(date),month(date)+1,0)
    then do the math for your Thursday or Wednesday.

    HTH
    Yes offcourse I want to get this out put

    Week First day (Thur) week last day (Wed)

    1/10/2018 03/10/2018
    4/10/2018 10/10/2018
    11/10/2018 17/10/2018
    18/10/2018 24/10/2018
    25/10/2018 31/10/2018

    Functions I have created for first day of week and last day of week are returning dates as follow:
    Week First day (Thur) week last day (Wed)

    27/09/2018 to 03/10/2018
    4/10/2018 10/10/2018
    11/10/2018 17/10/2018
    18/10/2018 24/10/2018
    25/10/2018 31/10/2018
    And if last week of month is in next month then like first week of Oct-18 contains these dates 27-09-2018 to 03-10-2018, I want divide this week as 27-09-2018 to 30-09-2018 and from 01-10-2018 to 03-10-2018.

    Can you suggest me any correction.

    I have done this excel throug weekday and eomonth function which is also quoted in firstofweek and lasofweek functions for better understanding and reference.

  8. #8
    Board Regular
    Join Date
    May 2013
    Posts
    424
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MS Acces vba function to get thursday as first day of week in current month and Wednesday as last week in current month.

    No.
    Not without a lot of code.
    You would need to calculate the weekdays of first and last of month, which you appear to have.
    If not exact days (Oct last day is a Wednesday) then do the math, then work out you have gone into a different month, then adjust for that.
    How you are meant to return these weeks (could be up to 6?) I do not know, array perhaps.?
    Excel 2007

  9. #9
    New Member
    Join Date
    Oct 2018
    Location
    Hyderabad, Sindh, Pakistan
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MS Acces vba function to get thursday as first day of week in current month and Wednesday as last week in current month.

    I have tried your code for first and last day of week it return 01/01/1900.

  10. #10
    New Member
    Join Date
    Oct 2018
    Location
    Hyderabad, Sindh, Pakistan
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MS Acces vba function to get thursday as first day of week in current month and Wednesday as last week in current month.

    I have done it with Excel formula for First day of week "'=MAX(C3-WEEKDAY(C3,14)+1, EOMONTH(C3, -1)+1)"
    and last day of week "'=MIN(B2+(7-WEEKDAY(B2,14)), EOMONTH(B2, 0))"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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