Change format on data that is imported from Access

[jcaptchaos2]

I have some data that is brought in from an Access database query, in the access query the data is formatted as short time. When it is pulled into my excel spread sheet it is formatted as text. '8:12 I need to add that data and the only way I am able to do that is highlight the column of data in the table and use the text to column feature to remove the '. The only issue is when the table is refreshed I will have to do it over and over again. Is there another way to do it?

 

[Norie]

How is the data getting pulled into Excel?

Is the field actually a date/time field?

 

[jcaptchaos2]

Norie,

It is being pulled using the get external data option in excel, in the access query it is formatted as short time.

 

[Norie]

How is it being formatted in the query?

Is the Format function being used?

If it is then you probably are importing text, that's what the Format function returns.

 

[jcaptchaos2]

In query it is being formatted as "Short Time" I am not sure how to check if it is using the Format function.

 

[xenou]

Excel isn't recognizing this as a time.
I would try a different format (cross fingers). Instead of Short Time enter the format:

hh:nn AM/PM

I think Excel will correctly interpret it this way.

 

[jcaptchaos2]

Xenou,

Thanks for responding, it still comes out as '6:12 test.

 

[xenou]

Are you sure that you have a time data type in the database, and not a text data type?

 

[jcaptchaos2]

It's a calculated field in a query

 

[Norie]

What's the expression for the calculation?