+ Reply to Thread
Results 1 to 5 of 5

Getting a Week number from a text string using DATE and WEEKNUM

  1. #1
    Registered User
    Join Date
    06-26-2013
    Location
    Edinburgh, UK
    MS-Off Ver
    Excel 2016
    Posts
    43

    Getting a Week number from a text string using DATE and WEEKNUM

    Hello

    I'm working on some data and have hit a problem I can't work out

    I am beginning with text data in this form - 3 examples are listed below (Note the US date format):
    10/18/2018 12:00:00 AM
    10/9/2018 12:00:00 AM
    9/30/2018 12:00:00 AM

    STEP 1
    I am using this formula to convert to a date in UK format :
    =DATE(VALUE(MID(O822,7,4)), VALUE(LEFT(O822,2)), VALUE(MID(O822,4,2)))

    STEP 2
    I am then using this formula to get the Week Number from the calculated data in Step 1:
    =WEEKNUM(G822,1)


    I can't work out how to get around the problem that Step 1 doesn't work on examples 2 and 3 because the day and month only have 1 digit.

    I would be grateful for any ideas how to solve this and get to the week number using formulae

    Thank you

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Getting a Week number from a text string using DATE and WEEKNUM

    I usd 2 helpcells to solve this problem.

    I indicated where the / is in the cell.

    See the attached file in the green cells C6 and D6.
    Attached Files Attached Files
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Expert
    Join Date
    12-09-2014
    Location
    Trakai, Lithuania
    MS-Off Ver
    2016
    Posts
    1,278

    Re: Getting a Week number from a text string using DATE and WEEKNUM

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

  4. #4
    Registered User
    Join Date
    06-26-2013
    Location
    Edinburgh, UK
    MS-Off Ver
    Excel 2016
    Posts
    43

    Re: Getting a Week number from a text string using DATE and WEEKNUM

    Hello oeldere

    This worked perfectly - I think this was a very ingenious solution.

    Thank you very much for your help

  5. #5
    Registered User
    Join Date
    06-26-2013
    Location
    Edinburgh, UK
    MS-Off Ver
    Excel 2016
    Posts
    43

    Re: Getting a Week number from a text string using DATE and WEEKNUM

    Hello Czeslaw

    I tried this Giant formula and it worked fine also - thank you!

    K

+ 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. Replies: 5
    Last Post: 12-05-2017, 07:39 PM
  2. [SOLVED] How to find Week number from Custom Date String
    By GemBox in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-01-2017, 08:48 AM
  3. Replies: 5
    Last Post: 04-11-2017, 09:23 AM
  4. Replies: 4
    Last Post: 11-25-2015, 06:44 PM
  5. [SOLVED] Convert a Text String Date to Date Serial Number
    By herve73 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-10-2015, 10:53 AM
  6. week start date and end date based on week number
    By aman1234 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-02-2009, 12:39 PM
  7. [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

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