+ Reply to Thread
Results 1 to 5 of 5

Function required to put each month days in to four week

  1. #1
    Forum Contributor
    Join Date
    06-24-2012
    Location
    Australia
    MS-Off Ver
    Excel2010
    Posts
    280

    Function required to put each month days in to four week

    Hello Excel Gurus,

    I have dates running in to one column and managed to get them in weeks-number as suggested by forum contributor Sixthsense.
    I require function to look at date and week-number to give for all Jan 1-7 dates in result as "Jan 2009 W1" for 1st week and so on
    Please see attached file...
    Thanks in advance...
    Karnik
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,041

    Re: Function required to put each month days in to four week

    in B2
    =TEXT(A2,"mmm")&" "&YEAR(A2)&" W"&INT(DAY(A2)/7)+1
    and copy down the column
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Function required to put each month days in to four week

    =text(a2,"mmm yyy")&" w" &c2
    or
    =TEXT(A2,"MMM YYY")&" W" &TRUNC(((A2-DATE(YEAR(A2),1,0))+6)/7)
    Last edited by martindwilson; 11-26-2012 at 08:08 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Forum Contributor
    Join Date
    06-24-2012
    Location
    Australia
    MS-Off Ver
    Excel2010
    Posts
    280

    Re: Function required to put each month days in to four week

    Hello SpecialK and marindwilson,
    Both suggestion works and very different... brilliant variety of solutions...
    Thanks a million
    Karnik

  5. #5
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Function required to put each month days in to four week

    INT(DAY(A2)/7)+1 gives 7 jan as wk 2

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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