LinkBack URL
About LinkBacks
How can I interpret this algorithm:
Wed Nov 17 01:44:07 +0000 2010
so that Excel will recognize it as a date & time.
Format Cells =>Number => custom ...then what do I enter????
Please help ASAP!!!!!!!!!!!!!!!!!
String to Date Conversion
How can I interpret this algorithm:
Wed Nov 17 01:44:07 +0000 2010
so that Excel will recognize it as a date & time.
Format Cells =>Number => custom ...then what do I enter????
Please help ASAP!!!!!!!!!!!!!!!!!
Last edited by DonkeyOte; 11-18-2010 at 03:41 AM. Reason: modified title
What about this:
=DATEVALUE(MID(A1,5,3)&" "&MID(A1,9,2)&", "&RIGHT(A1,4))+TIME(MID(A1,12,2),MID(A1,15,2),MID(A1,18,2))
Ensure that all entries follow the same format. All variables are on the same spot, that the month and day is always 3 and 2 char respectively; even if is September 6, it will be Sep 06, etc.
Welcome by the way. Take some time to read the rules. Your post's tittle should describe your problem and not the desired solution
Ron
Knowledge is knowing that a tomato and pepper are fruits. Wisdom is knowing whether to put these in a fruit salad
Kindly
[1] Use code tags. Place "[code]" before the first line of code and "[/code"]" after the last line of code. Exclude quotation marks
[2] Mark your post [SOLVED] if it has been answered satisfactorily by editing your original post in advanced mode.
[3] Thank (using the little scale) those that provided useful help; its nice and its very well appreciated
You could shorten that given coercion etc and known string lengths:
(the only unknown is dd or d but the above should account for either/or)
Please Login or Register to view this content.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
I don't think that my problem has been adequately answered ....
In one cell, I have as follows:
Wed Nov 17 01:10:55 +0000 2010
I am looking for info to input into the "format cells" menu so that it can interpret this data as a date/time for times like "sorting" data
no chance! it workd the other way around only
the date value which is a number can be formatted to display in different ways.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
There is a custom tab...
Couldn't a date/time algorithm be formulated IN the custom tab?
The custom number format section is to format dates. Excel does not recognize your string as date but as a text string, so in order to convert it to a date you'll need a formula. Once excel has a date; then you can use the custom format and show it whichever way you see fit. Hope this made it clear why you cannot format your text string.
that makes sense now...could someone walk me through the steps necessary to make this happen?
I would suggest you read this page first
Enter your data in A1 and enter the formula supplied by DO in the cell were you want the answer
Well, I'd insert a column after your date-text-string column and convert it to an excel proper date using DKO's formula above, copy this new column and paste values to your original column, and then delete this helper column.Originally Posted by dasreinfeld
Then you can use Custom format if you don't see an option under Date that satisfies your demands.
mission accomplished. Thanks guys!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks