Access Date Formatting / Extraction

applebyd

Active Member
Joined
May 27, 2002
Messages
348
Good morning all.

I'm a bit stuck as I cannot use a LEN command with the problem I have. I have had a look around the site and cannot see a simple (or even complex) fix.

I have an extract from a BI system that in four columns produces a text field with a
date in the format: January 1, 1900 12:00:00 AM

What I am looking to do is extract DAY, MONTH and YEAR from the source data. LEN doesn't work as the actual number of characters in the month changes.

Can anyone suggest how I can attack this? Online help hasn't been too productive as all
the solutions I have found assume month to be in an MM format and not alphabetical.

Any help greatly appreciated.

Regards

DaveA
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Just checked.

The format when I look at the download file in Excel is actually 'General', not, Text.

Sorry

Thanks again..

DaveA
 
Upvote 0
You can use the VBA Split Function. I put your entry in cell A3 and then ran this code module.
The Split Function puts results in an Array (datearray) and then you can pull out the piece parts you want to use.

The code returned:
Day = 1,
Month = January
Year = 1900

We could in code clear the "," from the Day result and could convert the Month from the spelled out Month (January) to a numeric.

Here's the code:
Code:
Option Explicit

Sub BreakDates()

Dim datearray() As String
Dim day As String
Dim month As String
Dim year As String

datearray() = Split(Range("A3"))
day = datearray(1)
month = datearray(0)
year = datearray(2)
MsgBox day & " " & month & " " & year
End Sub
 
Last edited:
Upvote 0
Thanks.

Any way this can be done in Access rather than Excel?

I could write a convertor for the file to 'tidy' the data before import.
That would add an extra stage that I don't mind, however, would prefer a one stop approach.

Anyway, that works a treat if I have to go for PlanB :)

Take care
Regards
DaveA
 
Last edited:
Upvote 0
You have a few choices.

1. To write some VBA code to import the data by looping file the text file recordset and do the conversion on the field as you are importing it (most complex method)

2. Import the file to "temporary" table first, importing this field as a Text field.
Then, convert the field to a Date value and use an Append Query to write the records to your "permanent" table.
If your field is called "Field1", here is the calculation that will drop the time component and coerce it to a valid date entry:
Code:
CDate(Left([Field1],InStrRev(Left([Field1],InStrRev([Field1]," ")-1)," ")-1))
 
Upvote 0
You have a few choices.

1. To write some VBA code to import the data by looping file the text file recordset and do the conversion on the field as you are importing it (most complex method)

2. Import the file to "temporary" table first, importing this field as a Text field.
Then, convert the field to a Date value and use an Append Query to write the records to your "permanent" table.
If your field is called "Field1", here is the calculation that will drop the time component and coerce it to a valid date entry:
Code:
CDate(Left([Field1],InStrRev(Left([Field1],InStrRev([Field1]," ")-1)," ")-1))


Superb, exactly what I needed!
Many thanks.

As usual I'm amazed by the support and knowledge here!

Regards

DaveA
 
Upvote 0
You are welcome!
Glad we were able to help.:)
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

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
Back
Top