format a time field in a query

[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

 

[Evi]

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

 

[Dale Fye]

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