+ Reply to Thread
Results 1 to 5 of 5

Formula to give week number of a month according to date of the month

  1. #1
    Registered User
    Join Date
    12-03-2007
    Location
    Singapore
    MS-Off Ver
    Professional Plus 2010
    Posts
    42

    Formula to give week number of a month according to date of the month

    Could someone help me with a formula that will take the date in column A and return the week number of the month in column B, based on this range:

    day of the month week of the month
    1st-7th 1
    8th-14th 2
    15th-21st 3
    22nd-29th 4
    30th-31st 5

    e.g.
    A B
    1 03/01/2016 1
    2 14/01/2016 2
    3 17/01/2016 3
    4 30/01/2016 5

  2. #2
    Registered User
    Join Date
    08-13-2010
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    6

    Re: Formula to give week number of a month according to date of the month

    Yes, Watch this video. You need to play around with the return type. (=WEEKNUM(F7,15)) In this case 15 is used as Friday is the start of the year.
    https://www.youtube.com/watch?v=EnxSHgeAdH0

  3. #3
    Valued Forum Contributor spitfireblue's Avatar
    Join Date
    01-29-2015
    Location
    Adelaide, Australia
    MS-Off Ver
    2007,2010,2016
    Posts
    611

    Re: Formula to give week number of a month according to date of the month

    Do you want 4 to be 22nd-28th?

    Try this:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Regards,
    Stephen

    If you feel someone has helped you please thank them and click on the star on their post to add reputation.
    Please ensure that you use code tags where relevant, and mark solved threads as solved.
    Most of all please be respectful and understanding of others.

  4. #4
    Registered User
    Join Date
    12-03-2007
    Location
    Singapore
    MS-Off Ver
    Professional Plus 2010
    Posts
    42

    Re: Formula to give week number of a month according to date of the month

    Yes, my mistake. Week 4 should be 22nd-28th, not 22nd-29th.

  5. #5
    Registered User
    Join Date
    12-03-2007
    Location
    Singapore
    MS-Off Ver
    Professional Plus 2010
    Posts
    42

    Re: Formula to give week number of a month according to date of the month

    Quote Originally Posted by spitfireblue View Post
    Do you want 4 to be 22nd-28th?

    Try this:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Thank you. That's exactly what I wanted.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. formula that generates month and week number from date
    By ea223 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-03-2013, 07:19 AM
  2. [SOLVED] Date Formula to Return Week Number in Current Month based on Reporting Period
    By jeversf in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-25-2013, 12:10 PM
  3. Replies: 10
    Last Post: 01-22-2012, 10:05 AM
  4. [SOLVED] Excel month dropdown list to generate day of week and date for selected month
    By aaaaaaaa in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-17-2010, 02:45 PM
  5. vba to find the month and week number from given date
    By Anil2007 in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 04-27-2009, 07:43 PM
  6. Replies: 3
    Last Post: 09-25-2007, 10:26 AM
  7. Replies: 1
    Last Post: 03-10-2006, 05:15 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1