Convert yymmdd text to useable date format

[crosser]

I have a linked database table with this field.
I will need to calculate the difference between this date and another which happens to be in this format mm/dd/yyyy

How do I do this? I will need convert it through an access formula since it's a linked table that cannot change and is updated daily.

The help on this forum is fantastic, thanks for helping a newb

 

[Trevor G]

You need a query and perhaps you can look at the Date Diff Function or possible look to format the field in an Expression.

Look at this SQL view of a query which has only selected a single field which has dates in the yy/mm/dd format and changes it in an expression to dd/mm/yy

SELECT Table1.DateSomething, Format([DateSomething],"dd/mm/yy") AS SwapFormat
FROM Table1;

 

[crosser]

Thanks for the quick reply Trevor but I don't think that will work. Its a text data field such as 100101 which is yymmdd.

I have NO idea why this field was setup in this manner in the first place.

Ultimately I will need to calculate the difference between this field and another which is a date type mm/dd/yyyy. Once I learn how to get the conversion then I was going to do the date diff function.

 

[Joe4]

To get the difference, all you need to do is subtract one from the other, if they are both formatted as Dates and not Text. You can convert a Text format to a Date format using the DateSerial function (format: DateSerial(year,month,day)).

For your exmaple in "yymmdd" format, that would look something like this:

MyCalcDate: DateSerial(Left([MyTextDate],2),Mid([MyTextDate],3,2),Right([MyTextDate],2))

So if you had a "real date" and wanted to subtract your "text" date, you

could have a formula that would look something like this:
MyDateDiff: [MyDate]-DateSerial(Left([MyTextDate],2),Mid([MyTextDate],3,2),Right([MyTextDate],2))

 

[sailepaty]

Try this,

CDate(Format(([MyTextDate]+20000000,"00-00-00"))

Regars

 

[crosser]

Joe4 - Perfect! Thank you so much! Was able to plug that in and worked as expected. Have a good one