Converting to/from date and text formats for subsequent Access queries

[cbrueckman]

thread68-1623236
thread68-1259000

I found these threads very helpful. I have Excel data with dates in a textual yyymmdd format. I also had similar Excel data, from another source where the dates were in a date format. I needed to be able to query both sets for specific days of the week, dates or other date related conditions and kept getting "data type mismatch" errors when I tried to run the queries in Access.

My solution was to build a calendar table in Excel add a column to converts the dates to the text yyymmdd format and another column to put the dates in a text "ddd" format. Other columns were added for specific conditions.

Importing the tables to Access allowed me to query the various data sets based on conditions in the one calendar table.

 

[Gruuuu]

Excel can convert text-based date representations to date values.

If D2 has 120223,
[tt]DATEVALUE(MID(D2,3,2)&"/"&RIGHT(D2,2)&"/"&LEFT(D2,2))
[/tt]
will return
2/23/2012 as a date value.

Also, this can be used in array formulas if you need to do lookups.

Example
[tt]
B D
Date1 Date2
2/24/2012 120223
2/23/2012 120228
2/27/2012 120228
2/24/2012 120223
2/24/2012 120223
2/26/2012 120224
2/27/2012 120226
2/26/2012 120226
2/23/2012 120225
...
[/tt]
Then
[tt]
{=MATCH(B2,DATEVALUE(MID($D$2:$D$36,3,2)&"/"&RIGHT($D$2:$D$36,2)&"/"&LEFT($D$2:$D$36,2)),0)}
[/tt] returns 6