[Webinar] Streamline your web hosting managementRegister Today

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 439

# Date Extraction Incorrect

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?
Copy-of-dateExtraction201.xlsx
0
Rayne
• 3
• 2
1 Solution

Commented:
Which part of the string determines the year?
0

Commented:
Try this
=DATE(IF(VALUE(RIGHT(B3,2))<20,CONCATENATE("20",RIGHT(B3,2)),CONCATENATE("19",RIGHT(B3,2))),MONTH(RIGHT(B3,6)),1)
0

Author Commented:
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

0

Author Commented:
Its working awesome….ScriptAddict, can you explain how you are getting it?
Thank you
0

Commented:
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.
0

Author Commented: