Importing Excel to Access - field goes to TEXT and not as date/tim

[LonnieJo]

Can an Access MACRO be used in an Excel spreadsheet?

I am trying to do a custom mm/dd/yyyy h:mm field minus another custom field
(same) to get how many days, hours and minutes have passed. Or even to just
convert the results to total hours (to the nearest hour) that have passed. I
am not as savvy in Excel as I have been in Access. When I import my excel
spread sheet into Access the field goes to TEXT and I can't get it back to a
date/time format without loosing the data. Would this be a better question
for the Excel folks? I am loosing time and need this report done soon.
Thanks for any guidance you can give me.

 

[Bryan in Bakersfield]

I'm not sure what you mean by using an Access MACRO be used in an Excel
spreadsheet, but I've had similar problems with dates in imported Excel
files. I usually create a query to convert the data then use the query as
the data source for other queries and forms (
Format([STARTDATE],"mm/dd/yyyy") )

SELECT CVMissingDocs.STUDENTNAME, Format([STARTDATE],"mm/dd/yyyy") AS
dSTARTDATE, Format([DATEREQ],"mm/dd/yyyy") AS dDATEREQ,
CVMissingDocs.DOCTYPEDESCRIP, CVMissingDocs.DOCCODE,
CVMissingDocs.PROGDESCRIPTION, CVMissingDocs.STATUSDESC,
CVMissingDocs.CAMPUSDESCRIPTION, CVMissingDocs.DOCSTATUS,
CVMissingDocs.COMMENTS, CVMissingDocs.SSN
FROM CVMissingDocs WHERE (((CVMissingDocs.SSN) Is Not Null));

Of course you would need to add the hours and minutes to the format - I
think you can use Format(Now(), "Long Time") then use the DateDiff function
to get the difference between the two.

Hope that helps.

Bryan

 

[Clif McIrvin]

Can an Access MACRO be used in an Excel spreadsheet?

I am trying to do a custom mm/dd/yyyy h:mm field minus another custom
field
(same) to get how many days, hours and minutes have passed. Or even
to just
convert the results to total hours (to the nearest hour) that have
passed. I
am not as savvy in Excel as I have been in Access. When I import my
excel
spread sheet into Access the field goes to TEXT and I can't get it
back to a
date/time format without loosing the data. Would this be a better
question
for the Excel folks? I am loosing time and need this report done
soon.
Thanks for any guidance you can give me.

If there are any cells in those columns that contain something other
than an Excel Date/Time value Access is going to get confused during the
import.

Have you tried linking instead of importing? Sometimes that works better
with Excel sheets.

There is a way to make Excel functions available to Access; but Access
has it's own Date / Time functions, so it seems that you shouldn't need
to worry about getting your Excel macro to run under Access at all.


You say that your "custom field" is text in access. What happens if you
try using the Access CDate function on it? That should convert the text
data into a standard Date/Time value.

HTH

 

[LonnieJo]

Thank you all. I will try these after lunch today (PST).