format a time field in a query

  • Thread starter Thread starter Martyn
  • Start date Start date
M

Martyn

I have an Excel spreadsheet that has a number of cells where the time value
is greater then 24 hours i.e. 36:58, total number of hours and minutes.

I've imported the spreadsheet into my Access DB and have tried to run a
query against it and other tables I have but the field imports as a text one
and I can't run a query because of a type mismatch.

I also can not convert the field to a date/time one as it wants to delete
the data in a number of fields because the number of hours is greater than 24.

Can anyone suggest a solution please?

Martyn
 
In Excel, you could convert all the hours+minutes into minutes with
=(Int(TimeCell) * 60) + (TimeCell - Int(TimeCell))
, then import that into Access as a number field

Back in Access, you can keep your minutes seperate if you wish and
concatenate them for Display purposes

Also, ensure that your date fields have a numerical format. When I tried to
import a date field formatted as Monday 07/09/07, it imported as text.
Use a macro in Excel to format all IsDate fields in your workbook as
dd/mm/yyyy or mm/dd/yyyy depending on your date/time settings in Regional
Settings on your PC
Evi
 
Martyn,

What do you want to do with this data in Access. Are you trying to sum it,
or do some calculations?

Generally, when I import from Excel to Access, I create the destination
table first, and define the data types there. Then, the import wizard will
generally format the data correctly as it is imported. Have not tried that
with a field that looks like what you have, but It might work.

If not you could write a simple function to do the conversion for you, but
you will have to decide what format you want. Do you want it to be a double
precision number that depicts hours and minutes (36.9666667), or do you want
it to depict the data as portion of a day (like Date/Time fields), where
36:58 would be represented as 1.5402777778?

Public Function TimeConvert(SomeValue as variant) as double

dim intPos as integer
If LEN(SomeValue & "") = 0 then
TimeConvert = 0
Else
intPos = instr(SomeValue, ":")
if intPos = 0 then
TimeConvert = dcbl(SomeValue)
elseif intPos > 0 then
TimeConvert = cdbl(Left(Somevalue, intPos - 1)) +
(cdbl(Mid(Somevalue, intPos + 1))/60)
endif
endif

End sub
 
Back
Top