+ Reply to Thread
Results 1 to 10 of 10

Date from Week and Year, Std formatting

  1. #1
    Registered User
    Join Date
    01-27-2015
    Location
    Southern Norway
    MS-Off Ver
    2010/2013 Std
    Posts
    17

    Date from Week and Year, Std formatting

    I have two fields in a table containing order details, both formatted as standard and containing (M)MYY:

    “ConfirmedWEEK”

    “DeliveredWEEK”

    Suggestions to extract the corresponding date for use in a Pivot Table ?

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Date from Week and Year, Std formatting

    both formatted as standard and containing (M)MYY:
    Not sure what that means. So the cells are currently displaying MYY (monthyear)?
    Theroretically, if you format the cell as date, it will show the dates.
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    01-27-2015
    Location
    Southern Norway
    MS-Off Ver
    2010/2013 Std
    Posts
    17

    Re: Date from Week and Year, Std formatting

    Cells have Standard format, data input as (M)MYY. Example: 115>>5215.

    In theory,yes. In practice,no.

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Date from Week and Year, Std formatting

    okay, this is an example
    data input as (M)MYY. Example: 115>>5215.
    How would I interpret that? Is that Nov 5th? What are the last 4 digits?
    Not sure how ">>" relates to the format "(M)MYY"

  5. #5
    Registered User
    Join Date
    01-27-2015
    Location
    Southern Norway
    MS-Off Ver
    2010/2013 Std
    Posts
    17

    Re: Date from Week and Year, Std formatting

    Interpreting...

    An example of what the user must input, (M)MYY, in STD formatted column(s)

    ">>" means From 115 To 5215 (115..215..XX15..5215)
    Last edited by torers; 09-25-2015 at 02:47 PM. Reason: Clarifying

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Date from Week and Year, Std formatting

    Ahhh, (lightbulb I think) so the first 1 or 2 digits are week numbers and the last 2 are the year?

  7. #7
    Registered User
    Join Date
    01-27-2015
    Location
    Southern Norway
    MS-Off Ver
    2010/2013 Std
    Posts
    17

    Re: Date from Week and Year, Std formatting

    Bulb lighted.

    Further clarification?

  8. #8
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Date from Week and Year, Std formatting

    so if confirmed week is 715 then you want the first date of that week? How do you want your weeks to run, Sunday to Saturday? Monday to Sunday?

  9. #9
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Date from Week and Year, Std formatting

    Assuming you want Monday's date, with your weeknumber in A1

    =("1/1/"&RIGHT(A1,2))-WEEKDAY(("1/1/"&RIGHT(A1,2))+0,2)+((LEFT(A1,LEN(A1)-2)-1)*7)+1

    for example, 1115 returns 3/9/2015 which is the monday of week 11 in 2015
    Remember to format cell as a date
    Note: Change Commas to Semicolons if necessary

  10. #10
    Registered User
    Join Date
    01-27-2015
    Location
    Southern Norway
    MS-Off Ver
    2010/2013 Std
    Posts
    17

    Re: Date from Week and Year, Std formatting

    Thank you!

+ 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. [SOLVED] Get the date of saturday in a week from a week number / year
    By JasperD in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-18-2015, 06:56 PM
  2. Find date from year and week number
    By pnperl in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-17-2014, 04:04 AM
  3. calculating month to date, year to date, week to date
    By labogola in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-06-2014, 05:21 AM
  4. Help with date forecasting and week of the year
    By sandman1145 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-24-2013, 10:52 AM
  5. Replies: 1
    Last Post: 12-15-2011, 05:32 AM
  6. [SOLVED] how to insert month date year and day of week
    By Sachi Noma in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 05-18-2006, 10:10 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

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