Date Extraction Incorrect

Posted on 2012-08-23
Last Modified: 2012-08-24
Hello All,
Date value not converting properly.
Cell B3 of main sheet has combined string of period abbreviation and date. I used a Right method to get the date out of the string. Its extracting the date value but when the date value is formatted back to date, its showing incorrectly. Like 2011 is showing as 2012.
Where the issue?
Question by:Rayne
    LVL 8

    Expert Comment

    by:Elton Pascua
    Which part of the string determines the year?
    LVL 11

    Accepted Solution

    Try this

    Author Comment

    Hello TechFanatic,
    period Abbreviation      period date      Combined String
    3(f)      11/1/2011      3(f) Nov-11

    So probably the last two pieces of the combo string

    ScriptAddict – I am  trying your suggestion …

    Author Comment

    Its working awesome….ScriptAddict, can you explain how you are getting it?
    Thank you
    LVL 11

    Expert Comment

    sure, the problem is that it thinks the sep 11 is september 11 2012

    I used the date function and forced the year calculation based on the last two digits.  I'm assuming that if the last two digits are less then 20, then the prefix for the year is 20

    if it's higher then 20 I'm assuming that the correct prefix for the year is 19

    not exactly perfect,  but unless you plan on using this for a long long time it should work acceptably.

    Author Comment

    Thank you ScriptAddict :)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Suggested Solutions

    Title # Comments Views Activity
    Office 365 Sync with office 2016 3 19
    Always hangs on opening 8 30
    copying from excel to word 2 28
    Excel formula 5 31
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now