First Workday of the Month

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: First Workday of the Month

  1. #1
    Board Regular
    Join Date
    May 2011
    Posts
    79
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default First Workday of the Month

     
    Hi,

    I have seen many articles or posts on the internet that using the following formula, I could get the first workday of the month.

    =WORKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1)-1,1)

    However, the result of the formula is 43040. It doesn't make sense to me. Do I need to convert it first before I get say a day between 1st to 3rd?

    Thank you!

  2. #2
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    8,963
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: First Workday of the Month

    You need to format the cell as a date. Right click, format cells - select one of the date formats and 43040 will give you November 1st.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, Excel jeanie, RoryA addin (Win & Mac) or Borders-Copy-Paste

  3. #3
    Board Regular Scott T's Avatar
    Join Date
    Dec 2016
    Posts
    1,646
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: First Workday of the Month

    Excel dates are just numbers formatted to be dates. Format the cell as a date and you will see that 43040 is 11/1/17.
    Colonel Sandurz: Prepare ship for light speed.
    Dark Helmet: No, no, no, light speed is too slow.
    Colonel Sandurz: Light speed, too slow?
    Dark Helmet: Yes, we're gonna have to go right to ludicrous speed.

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

    Default Re: First Workday of the Month

    Hi,

    thank you!

    it works.

    I wanted use the formula in VBA. I tried this
    Code:
    this_Month = application.WorksheetFunction.WorkDay(DATE(YEAR(TODAY()),MONTH(TODAY()),1)-1,1)
    but somehow this is not working. Wonder if someone could help.

  5. #5
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    20,812
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: First Workday of the Month

    Quote Originally Posted by 30percent View Post
    =WORKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1)-1,1)
    Formula wise this version is easier

    =WORKDAY(EOMONTH(TODAY(),-1),1)

  6. #6
    MrExcel MVP Tetra201's Avatar
    Join Date
    Oct 2016
    Posts
    2,919
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: First Workday of the Month

    VB-wise, try this:

    Code:
    Dim this_Month As Date
    this_Month = WorksheetFunction.WorkDay(Date - Day(Date), 1)

  7. #7
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    8,963
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: First Workday of the Month

      
    Adding to what Barry stated for the formula, in VBA try....

    Code:
    Sub xxxx()
    Dim this_Month As Date
    this_Month = WorksheetFunction.WorkDay(WorksheetFunction.EoMonth(Date, -1), 1)
    Range("a1") = this_Month
    End Sub
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, Excel jeanie, RoryA addin (Win & Mac) or Borders-Copy-Paste

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
  •  

 

 
DMCA.com