Help with date formatting

[steveg242]

Hi, this should be an easy one but I'm having trouble with the following formatting.

I have the text coming from an excel file which I bring into a table and want to convert it into a proper date:

"Tuesday, June 25"

I want to convert that into the standard date 6/25/13. I've tried Format(), I've tried DatePart, but so far I'm not getting the desired result.

Format([Date], "m/d/yy") just spits it right back out as Tuesday, June 25

 

[rzw0wr]

In your table, set the field to date/Time.
Format that to Short Date.

Format() (function) changes it to text not a date.

Dale

 

[Brianwarnock]

This text string is not a date and as it has no year it cannot be converted to a date using CDATE.

Brian

 

[steveg242]

Thanks for the quick response, rzw0wr.

I changed the field type but now the data is not being accepted at all. It's a type mismatch. Probably because the excel file is missing the year. Maybe a little further explanation of what I'm trying to do...

I have an excel file which is automatically generated from a web form. Once I save that file, I have it as a linked table in my database. Next I am trying to dump it into a temporary table. A quirk is there are 6 fields and this date is in *one* of the six fields. So I am trying to write a subroutine that's going to identify which field has the date and copy it into it's final destination field which is formatted as a proper date/time field. Once I do this with the temporary table (and a few other things), it will be ready to dump into my main table.

So I am checking if a field is not null, taking that string value "Tuesday, June 1" and put it in the final date field as 6/1/13.

My code looks something like this (using DAO a recordset)

FinalDate = rst![Date1]
MsgBox FinalDate
rst![FinalDate] = Format(FinalDate, "m/d/yy")

I've also tried adding the year in the first line. I put in the msgbox for debugging.

 

[steveg242]

This text string is not a date and as it has no year it cannot be converted to a date using CDATE.

Brian

Yep, so that's my issue. How do I get it from this string format into the desired format? It's just a matter of manipulating the string the right way.

 

[Brianwarnock]

Convert the text string to a string that represents a date then use CDATE to convert that. If all of the years are 2013 then try

Cdate(mid( myfield, instr(myfield, ",")+2) & ", " & 2013)

This will give you a date hopefully, remember that all dates are stored the same way, as a double decimal number, format is just for display so once you have stored the date you can view it how you like using format.

Brian.,

 

[steveg242]

Thanks, Brian.

I knew it was just a matter of manipulating the string.

After assigning the field to a variable called Date0 and adding the year, here is my code:

FinalDate = Format(CDate(Mid(Date0, InStr(Date0, ",") + 2, Len(Date0))), "m/d/yy")

I was close, but the key was adding the year THEN using CDate.

 

[Brianwarnock]

Glad you got it working, yes you could add the year first rather than on the fly as I did.

I don't understand what you are doing with the Len part of your code, if it had been required it would have been the length of the field minus the starting point, however in the Mid function if you omit the length it defaults to the remainder of the field from the start point.

Brian

 

[steveg242]

...in the Mid function if you omit the length it defaults to the remainder of the field from the start point.

Hmm, whenever I've used the Mid function, I always accounted for the end by habit. I guess never realized it had that kind of default.

But yeah, no need to run any functions if it's not necessary. Thanks.