+ Reply to Thread
Results 1 to 8 of 8

Obtain Year and Week Number.

  1. #1
    Registered User
    Join Date
    06-03-2013
    Location
    Matamoros, Mexico
    MS-Off Ver
    Excel 2007
    Posts
    52

    Question Obtain Year and Week Number.

    Hello to all -

    I have a Table with dates. In Cell G3 I have the date 2/21/2014.

    I need a formula in cell B3 to get Year and Week Number as follows: 2014/02

    I have been searching but all that I have found results in 2014/2
    Weeks 1 to 9 are shown as 1, 2, 3...

    I need Week numbers to be shown like 01, 02, 03...

    Can you please help me with a formula to get the result that I need??

    I will appreciate the big support.

    SamCV.
    Matamoros, Mexico.

  2. #2
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Obtain Year and Week Number.

    Hello

    Try

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


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

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Registered User
    Join Date
    03-07-2014
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Obtain Year and Week Number.

    You can use a combination of TEXT and WEEKNUM functions to get your results

    In your example
    it will be WEEKNUM(G3,1). This gives you a result of 8
    To this, you use the TEXT function

    TEXT(WEEKNUM(G3,1),"DD"). This will give you result as 08

    To get the formula to your question

    =CONCATENATE(YEAR(G3),"/",TEXT(WEEKNUM(G3,2),"DD"))

    Hope you this helps

    KP
    Last edited by spodurinagasiva; 03-07-2014 at 01:04 PM.

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Obtain Year and Week Number.

    Or this

    =YEAR(G3)&" / "&TEXT(WEEKNUM(G3),"00")
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,711

    Re: Obtain Year and Week Number.

    Maybe:
    =YEAR(G3)&"/"&TEXT(WEEKNUM(G3),"00")

  6. #6
    Registered User
    Join Date
    06-03-2013
    Location
    Matamoros, Mexico
    MS-Off Ver
    Excel 2007
    Posts
    52

    Re: Obtain Year and Week Number.

    Very quick answers!! And all of them work perfectcly!!!

    Thank you all for your help and advise!!
    Very awesome work!!

    I had add points of reputation to all of you.

    Please have a great day and an excellent weekend.
    (Que tengan un gran día y un excelente fin de semana).

    SamCV.
    Matamoros, Mexico.

  7. #7
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Obtain Year and Week Number.

    Thanks for the feedback!

  8. #8
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Obtain Year and Week Number.

    Quote Originally Posted by spodurinagasiva View Post
    =TEXT(WEEKNUM(G3,1),"DD")
    It wouldn't be a good idea to use this, what if the date in G3 is 8/8/2014?
    Audere est facere

+ 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. week number based on year start 01/10
    By tony0710 in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 07-02-2014, 12:21 PM
  2. [SOLVED] Determine Week Start Date From Year And Number
    By SalientAnimal in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 12-23-2013, 07:51 AM
  3. Year and week, adding zero before week number
    By randalino in forum Excel General
    Replies: 1
    Last Post: 06-23-2009, 10:34 AM
  4. Week Number and Year
    By axc0054 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-28-2008, 05:24 PM
  5. [SOLVED] Calculating a Date Based on Year, Week Number and Day of the Week
    By amy in forum Tips and Tutorials
    Replies: 1
    Last Post: 08-23-2005, 11:42 AM

Tags for this Thread

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