Hi All
I have a 5000 line spreadsheet downloaded from an online document control system that I need to extract the date from a particular cell containing text and a six number figure at the end of the text being the date - the date function is always
I have used LEFT and RIGHT to isolate the 6 numbers representing the date in the next column, but the TEXT function is then giving a strange result when I try and format the date
See attached spreadsheet...
Maybe one way...
=(MID(TRIM(RIGHT(B4,7)),3,2)&"/"&LEFT(TRIM(RIGHT(B4,7)),2)&"/"&RIGHT(TRIM(RIGHT(B4,7)),2))+0
Format as required
HTH
Regards, Jeff
I see nothing strange in those results. 50,918 days since 1 January 1900 is May 28 2039, 61,218 days since 1 Jan. 1900 is 9 August 2067, and 50,119 days since 1 Jan 1900 is 20 March 2037. It's all about understanding how Excel stores and manipulates dates and times as serial numbers (and I don't know why there isn't more documentation out there that really explains how it works). I recommend this page: http://www.cpearson.com/Excel/datetime.htm
Since your text strings are really mmddyy text strings, you will need additional text manipulation functions to extract the individual month, day, year values, then use the DATE() function to get Excel to combine those elements into a correct date serial number. =DATE(RIGHT(C4,2),LEFT(C4,2),MID(C4,3,2)). You can then nest that inside of a TEXT() function if you want to convert the date back to a text string, or leave the function as is (so Excel will store the result as a usable serial number) and use number formatting to display the result in the desired format.
Hopefully that helps. If you are going to be dealing with dates and times in Excel, I recommend spending some time understanding how Excel's date/time serial number system works.
Edit to add: Since there can/will be some confusion with how Excel deals with 2 digit years (current standard is to treat any 2 digit years less than 29 or 30 as after 2000, but anything greater than that is considered 1900, I could see some value in adding 2000 to your 2 digit years (or otherwise use 4 digit years) so that Excel cannot inadvertently give you a 1900 date (unless you intend it to give you a 1900s date).
Last edited by MrShorty; 11-16-2019 at 11:24 AM.
Originally Posted by shg
Try this one..
Enter formula in C4 and copy down. Format as Date
Formula:
Please Login or Register to view this content.
v B C 3 4 Architect Comments 050918 9/5/2018 5 Landscape consultant comments 061218 12/6/2018 6 CDO comments 050119 1/5/2019
Last edited by AlKey; 11-16-2019 at 12:30 PM.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
I expect this will work with your date settings:
Architect Comments 050918 C4: =--TEXT(RIGHT(TRIM(B4), 6), "00\/00\/00") Landscape consultant comments 061218 CDO comments 050119
Entia non sunt multiplicanda sine necessitate
Jeffrey - Thanks! That has worked
It was the date number reference that was causing the issue - but our formula has solved the problem!
Mr Shorty
I guessed this was something to do with the serial number code - as when I typed in the six-figure date code without the leading zero, it came back with the date that I was being given. I did not know that the code was based on dates from 1.1.1900 - handy to know!
Alkey - thanks for your response - basically a similar version to Jeffrey's -all good though!
Shg - again, thanks for your response - I will have to have a look at that one as it is a little different!
Hi QSGuy,
You are very welcome and thanks for the feedback.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks