Results 1 to 3 of 3

Thread: Week Of The Month

  1. #1
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10

    Week Of The Month

    If you wanted to know the week number in a year, you'd just have to use WEEKNUM(serial_num,[return_type])

    If you wanted to know the week number in a month, you could use the following formula

    =IF(MONTH(A1)>1,WEEKNUM(A1,2)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),0),2)+1,WEEKNUM(A1 ,2))

    You could make it shorter with the EOMONTH formula as below

    =IF(MONTH(A1)>1,WEEKNUM(A1,2)-WEEKNUM(EOMONTH(A1,-1),2)+1,WEEKNUM(A1,2))

    For those who are inclined to the mathematics of it, you could use an even intuitive formula

    =ROUNDUP((A1-EOMONTH(A1,-1))/7,0)
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  2. #2
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Your ROUNDUP formula does not produce the same results as your other two formulas. The first two formulas appear to be calculating the breakpoint between the first week and the second week at the first Monday in the month. So, any days before the first Monday are part of Week #1 and the first Monday plus the next 6 days make up Week #2, the next 7 days after that make up Week #3, and so on. Your ROUNDUP formula, on the other hand, simply considers the first 7 days of the month to be in Week #1, then next 7 days being in Week #2 and so on. As an aside, your ROUNDUP formula can be simplified to this...

    =ROUNDUP(DAY(A1)/7,0)

  3. #3
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    You got that right Rick.... a little too confident for comfort from my part I guess.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

Similar Threads

  1. Nth Such-And-Such Day Of The Month
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 6
    Last Post: 01-02-2020, 06:48 PM
  2. Formula to Display Month and Dates Using Spin Button
    By ayazgreat in forum Excel Help
    Replies: 6
    Last Post: 11-21-2012, 10:19 PM
  3. Find Quarter Month From Date In Excel
    By Excel Fox in forum Excel and VBA Tips and Tricks
    Replies: 15
    Last Post: 04-19-2012, 02:08 PM
  4. Week Number And Week Start Day of Week
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 4
    Last Post: 10-24-2011, 07:33 PM
  5. Number of Days In A Month
    By Excel Fox in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 05-14-2011, 08:00 PM

Tags for this Thread

Posting Permissions

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