• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Date Parsing Dilemma

polarisking

polarisking

Member
I just cannot seem to figure this one out. I've attached a file with a column of what appear to be dates looking like Jun 30 1989 12:00AM (mmm d yyyy HH:MMAM/PM).

Try as I might with Text to Columns, changing the cell's format from general to date, adding 0 to the cell, DATEVALUE(), etc. I cannot, without a macro (which required me to parse out each element and even convert the three letter month to a numeric value), get Excel to recognize the contents of the cell as a date and convert to its appropriate serial number.

Any help would be very much appreciated.
 

Attachments

  • Chandoo - Date Parsing Issue.xlsx
    45.3 KB · Views: 3
Hmm. Several problems with the data that causes normal technique to fail. There's usually a comma/dash/something between day and year. Also, there's no gap between time and AM/PM. And finally, some entries have double spaces, while some have single. Crikey!

Anyway, this formula will convert all your example data as desired.
=IF(MID(A2,4,1)<>" ",A2,1*(TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(TRIM(A2)," ",", ",2)," ",REPT(" ",999),3),999))&" "&REPLACE(TRIM(RIGHT(A2,7)),LEN(TRIM(RIGHT(A2,7)))-1,0," ")))

EDIT: If all your timestamps are 12:00AM, then Narayan's method may be the better approach.
 
Luke/Narayan, thanks for the replies.
- Narayan, what does the + 0 do after both MID expressions?
- Luke, you hit the nail on the head - the spacing is inconsistent (you'd expect no space for a leading 0, but instead it's there giving Narayan's formula a foothold for position location)
 
Hi ,

The last +0 is to convert what would otherwise be a text output , into a numeric value , which you can format appropriately.

The first +0 is to convert a text string which has a space character followed by a digit , into a numeric value.

An input such as Jun 30 1989 12:00AM has one space character between the month and the date ; an input such as Nov 1 1989 12:00AM has 2 space characters. We are extracting 2 characters after the 5th character for the date ; this works well with the first input of Jun 30 1989 12:00AM , but when you do it with the second input of Nov 1 1989 12:00AM , the resulting text string is not treated as a valid date string by Excel , since the first character is a space. Using +0 converts the date to a numeric value.

Thus a text string such as " 1-Nov-1989" , with a leading space does not translate into a valid date , when +0 is used at the end ; a text string such as "1-Nov-1989" , without the leading space , translates into a valid date , when +0 is used at the end.

The problem is because of the +0 at the end to convert the text string into a numeric value ; instead you can use the DATEVALUE function to do this ; in this case , the leading space character is no problem ; both :

=DATEVALUE("1-Nov-1989")

and

=DATEVALUE(" 1-Nov-1989")

return a valid date.

Narayan
 
Thanks polarisking. Looking at all our formulas, one key question is whether or not the time portion is important/different. Can you confirm either way?
 
You must log in or register to reply here.
Back
Top