Importing Excel # to 4-digit short time format into Access

[floridaboy2004]

Hello all,

This is an ACCESS & EXCEL type problem...

I am have an issue and am begging for the expertise of the experts.

I have a .TXT file that I am opening/importing in EXCEL. One of the fields in this .TXT file is a 'TIME OF PURCHASE' field. During the import/opening process, this time that displays as Short Time format '00:35' (e.g. 12:35AM) in DOS, when opened in EXCEL is truncated to '35' without the leading zeros, etc.

I need to then import this EXCEL file into an existing ACCESS database that should appear in a form AND the corresponding table as a SHORT TIME (00:00), four-digit time (such as '00:35' for 12:35AM) and it is not recognizing '37' as '00:37'.

Can someone PLEASE guide me as to how to get a DOS-based field from a .TXT file to an EXCEL file and to convert a DOS-based four-digit time (e.g. 00:37) to an EXCEL four-digit time?

Thank-you again, all!

 

[SydneyGeek]

Re: Importing Excel # to 4-digit short time format into Acce

Hi floridaboy, the trick once the time info comes across, it to convert it back to seconds. Excel, Access and other apps regard time as a fraction of a day, so you could try something like this for times in Column A:
IF(A2>1,A2/86400,A2) and fill down. Format the column as Short Time, and you're set.

Denis