[Webinar] Streamline your web hosting managementRegister Today

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

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
Asked:
Rayne
  • 3
  • 2
1 Solution
 
Elton PascuaCommented:
Which part of the string determines the year?
0
 
ScriptAddictCommented:
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
 
RayneAuthor 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

ScriptAddict – I am  trying your suggestion …
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
RayneAuthor Commented:
Its working awesome….ScriptAddict, can you explain how you are getting it?
Thank you
0
 
ScriptAddictCommented:
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
 
RayneAuthor Commented:
Thank you ScriptAddict :)
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now