Intelligent Date Values

[pal777]

A certain bank (OK, Chase) formats its downloadable .csv file line item dates as text, like this:

1) "20090309120000[0:GMT]"

This example (1) translates as 2009 (year), 03 (month), 09 (day), 12:00 (hours:minutes), 00 (seconds) [0:GMT] (Greenwich Mean Time).

Using "Text to Columns", this can automatically be stripped and parsed as:

2) "3/9/2009"

The problem is, this is just a dead string of characters, rather than an encoded Julian Day Number , such as:

3) "39881"

While dead character strings like (2) can be sorted to obtain ascending values, etc., they cannot be arithmetically co-mingled with other real date values in Excel. Is there a simple way to get from (2) to (3), in a way that can be automated with VBA?

Thank you,

Peter L.

 

[PA HS Teacher]

=date(left(a1,4),mid(a1,5,2),mid(a1,7,2))

 

[Norie]

Peter

How exactly did you do the text to columns?

With your small sample I was able to parse the date portion to return a 'real' date.

I did it by using fixed width, isolating the date part of the string and then selected YMD for the Column data format on the last step.

PS What do you mean by 'dead string'?

 

[pal777]

Greetings, folks:

Sorry for the delay in responding.

Dear PA HS Teacher:
Your solution worked perfectly, after converting it to use R1C1 notation for VBA. Example:
Range("A1:A" & FinalRow & "").FormulaR1C1 = "=date(left(RC[2],4),mid(RC[2],5,2),mid(RC[2],7,2))"

I was poking around VBA & Excel Help to find a description of the Date syntax you used but was unsuccessful - is there a reference you could point me towards?

Many thanks!

Dear Norie:

1) This is what I was doing before:
'De-convolve Chase time stamp into date components (year/month/day):
Selection.TextToColumns Destination:=Range("F1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(4, 1), Array(6, 1), Array(8, 1))
Columns("F:H").NumberFormat = "General"
Columns("A:B").Delete
'Concatenate date components:
Columns("A:B").Insert Shift:=xlToRight
Range("A1:A" & FinalRow & "").FormulaR1C1 = "=RC[6]&""/""&RC[7]&""/""&RC[5]"

2) I was being descriptive with the term "dead string" - I just meant a string of characters that looked like a date, but didn't operate like a date.

I'd be curious to know the details of your solution.

Thanks again for your help!

Peter

 

[barry houdini]

Hello pal777, not really answering your latest questions but a slightly different version for the original. You can convert using this formula

=TEXT(LEFT(A1,8),"0000-00-00")+0

 

[Norie]

Peter

I gave details of my solution.

Idid it by using fixed width, isolating the date part of the string and then selected YMD for the Column data format on the last step.[/code]

 

[pal777]

Hi Nori: Yes, I did read your description - thank you. However, just as you wanted to know exactly what I was doing, I was curious if you'd be willing to share the exact code you're using. It would be helpful.

Hi Barry: Thank you for your contribution as well. It works! But how? Is there a reference you could point me towards?

Thank you,

Peter L.

 

[Norie]

I am not using any code.

I'm selecting the column with the 'date/time', going to Data>Text to columns, selecting Fixed width and setting a break between the date and time.

Then on the last step I select the 'date' column and set it's Data column format to YMD.

And i choose not to import the 'time' column.

 

[pal777]

Hi Nori:

Brilliant! Thank you so much.

Peter L.