Intelligent Date Values

pal777

New Member
Joined
Jun 30, 2004
Messages
10
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.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,305
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 30, 2004
Messages
10
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,305
Office Version
  1. 365
Platform
  1. Windows
Peter

I gave details of my solution.
Norie said:
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

New Member
Joined
Jun 30, 2004
Messages
10

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,305
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,144,376
Messages
5,724,001
Members
422,530
Latest member
Badpoisondwarf

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top