Date mess

[ZoomerZ]

I have this data in Access pasted from PeopleSoft where Date field is Text but contains mix of "mm/dd/yyyy" and "mmddyyy" dates for whatever reason beyond my control.

How do I convert all into "mm/dd/yyyy" if simple Format function wouldn't do?

 

[AnotherHiggins]

To convert all of these to date format, go to an empty column to the right of the existing data set and use this formula:

[COLOR=blue white]=IF(ISERROR(DATEVALUE(A2)),DATEVALUE(LEFT(A2,2)&"/"&MID(A2,3,2)&"/"&RIGHT(A2,4)),DATEVALUE(A2))[/color]
(replacing the 'A2' with whatever cell you need, of course). Then format this new column however you want.

FYI, for future reference:
If all you need to do is convert text that looks like numbers to actual numbers (you could use this if all of your data looked like mm/dd/yyy), you could use one of the following....

The 'times 1 fix':

[ul][li]type 1 in an empty cell (to the right of all existing data[/li]
[li]copy that cell[/li]
[li]highlight the section of data that you want to convert[/li]
[li]go to Edit > Paste Special and select values and Multiply[/li][/ul]
VB solution(SkipVought posted this a while back):

Sub z_ConvertToNumbers()
   For Each c In Selection
      With c
         .Value = .Value
      End With
   Next
End Sub

Just highlight the desired range before running the macro.

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[ZoomerZ]

We talking Access right?

 

[AnotherHiggins]

Oops.

No, that was Excel. Maybe I should actually start reading posts before responding?

You could always export from access to excel, use the advice above, then re-import to access - but that's kind of silly.

I'm currently revising to Access instructions. Please hold....

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[AnotherHiggins]

Use this in a query:
[COLOR=blue white]ConvertedDate: IIf(Mid([Date],3,1)="/",DateValue([Date]),DateValue(Left([Date],2) & "/" & Mid([Date],3,2) & "/" & Right([Date],4)))[/color]


[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[ZoomerZ]

It is giving me blank value
I've tried to use it as a parameter so it asks me for date and I copy/pasting it fron table - blank....
And what is ConvertedDate? I couldn't find this function. Do I need to add references?

thanks

 

[AnotherHiggins]

[li]Create a new Query.[/li]
[li]Select Design View[/li]
[li]Double Click on the Table Name you want to modify[/li]
[li]Press Close[/li]

Access is asking you to insert Date because that name doesn't exist in your table. Any time you see [Date] in my formula, change that to the name of the field in your table that contains the messed up dates.

The ConvertedDate is simply a new field name. It could just as easily read NewDate. The syntax works like this -
FieldHeading: Function

FieldHeading is any name you want to assign. I chose ConvertedDate but you may want to simply use Date.

Function is any function that Access understands. You can even simply refer to a field-name from the table just to change its name. In the example I provided, I used an If statement for the Function. Note: In access, If statements are entered with IIf (two 'I's)


[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[ZoomerZ]

Sometimes I just don't get it...sorry, you lost me.
Let say I will use
Select [Pay Date].[Master_Table]
From [Master_Table]


So where that formula would go?

 

[AnotherHiggins]

Follow the opening steps from my last post to create a new query in design view.

Highlight all of the fields listed in the table and drag them down to the 'columns' below.

In an empty column to the right of the other data, type:
[COLOR=blue white]PayDate1: IIf(Mid([PayDate],3,1)="/",DateValue([PayDate]),DateValue(Left([PayDate],2) & "/" & Mid([PayDate],3,2) & "/" & Right([PayDate],4)))[/color]

NOTE:You can use any name you want, it doesn't have to be PayDate1.



[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[ZoomerZ]

You wrote: In an empty column to the right of the other data, type.

We agreed that it was Access.
I want to use query as
Select [Pay Date].[Master_Table]
From [Master_Table]

 

[xlbo]

In DESIGN VIEW for the query. NOT the SQL view - click on the icon that looks like a set square to enter design view

In the design view you can view the fields as columns and insert a new one with a custom formula

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question