Importing duration values from Excel to Access 1

[CuckooNut]

Hi,

I need to import duration data (i.e. duration of a media file) from an excel spreadsheet into an Access database. The duration values in the spreadsheet are stored in the h:mm:ss format. Since Access does not have a duration data type, what is the best way to do this?

Thanks!

 

[randy700]

Access does have a date/time data type, as does Excel.


Randy

 

[CuckooNut]

I know it has a date/time data type. But is there a way to import the data in the h:mm:ss format? Every time I have tried, Access has converted it to a time of the day.

 

[jges]

How is the data saved in Excel? I don't think Excel has a native 'duration' data type either. Perhaps it is saved as the number of seconds and formatted to display h:mm:ss; or the h:mm:ss is input as a text string?

 

[CuckooNut]

The Excel data is stored in cells that are formatted with a custom number format code of [h]:mm:ss. If I change the format to Number, it simply changes to 0. However, if I change the format to Time, it displays it as a time of the day. For instance, if it shows the duration as 0:04:37, when I change the format to Time, it displays as 12:04:37 AM.

 

[dhookom]

All dates and times are numbers. The format property determines how the numbers are displayed. A single day has the numeric value of 1. 12 hours equals 0.5 and 6 hours = 0.25.

Numbers are numbers are numbers.

Generally when I want to store a duration, I decide on a time increment such as minutes. Then I enter and store the whole number of minutes. If I need greater detail, I might change to seconds or allow decimal places in the minutes.

You should generally not be storing durations in date/time fields.

Duane
Hook'D on Access
MS Access MVP

 

[CuckooNut]

Thanks for your responses. I am fine with storing the durations as seconds in the database. My question is, what is the best way to convert the duration from the date/time h:mm:ss format that the spreadsheet uses into the whole number format that I need in order to store the data as seconds in Access? Is there a way to do this in VBA as part of the import process?

 

[jges]

See example 2 on this page:

http://office.microsoft.com/en-us/e...difference-between-two-times-HP003056108.aspx

In short, subtract 12:00 am from the time value you have and multiply by 86400 to get the time difference in seconds. You can do it in VBA if you wish, or it may be easier to add a column and enter a formula.

Hmmm, I wonder if it will export out the results of a formula or try to export the formula itself. Some testing is in order...

 

[CuckooNut]

jges, that worked like a charm. Thank you! And thanks to all of you for your help!

 

[CuckooNut]

Also, just so you know, it exported the results as the number, and not as the formula.

 

[jges]

Excellent, thanks for reporting back. I had not made the time yet to test it.

 

[SkipVought]

Excellent, thanks for reporting back. I had not made the time yet to test it.

Meaning, that you have not made the point in time (a time value on a specific day) with the necesary period of time (a duration) to test it. ;-)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]