Time Duration HH:MM:SS in Access to [h]:mm:ss in Excel

[Joe2k10]

Hello all,

I am new here and am hpoing someone can help.

The problem I have is:

I have a query, it contains a start date and a end date.

I have tried various options to calculate the time difference in a hours and minutes less holidays and less weekend days.
I am confident I have go this bit right, I am getting the correct amont of hours and minutes in HH:MM:SS and can also do it by decimal number.

Trouble is, when I export the query to excel and throw the data into my excel report template I need the time to be in [h]:mm:ss format. I need it to do this because a pivot table reads from it to calculate and display the average time duration in a chart.

Does anyone know how to get around this?

I researched everywhere and cannot find out how to convert the time format from hh:mm:ss. I can do it manually in excel by changing the format of the column but then I have to click into each cell and press enter to change from the DB format of decimal time difference or HH:MM:SS format to the excel format of [h]:mm:ss. With 30,000 records this is not an option.

I am quite sure there is an easier way to do this but I have gone so far down the road of complication im finding it hard to get back.

Any help would be greatly appreciated.

Thanks

Joe

 

[namliam]

The problem is Access dont have the [h]:mm:ss format...

Basicaly 2 options... both from the fact, which you already seem to know, that date/time is actually a decimal number
i.e.
0.5 == 12:00:00
1.5 == 36:00:00
2.5 == 60:00:00

All you "need" to do is either
1) export the date as a normal date
2) export the date as a number using the CDBL function

Either way the 'date' going to excel is a number, though it 'looks' different and you can/should be able to do you graph no problem.

 

[Joe2k10]

Thanks for your help on this namliam

I have done what you said and left the time duration in decimal format.

I have changed the format to custom ([h]:mm:ss) in the field setting for average time duration in my pivot table. It converts it from the decimal time duration to [h]:mm:ss format

Again, thanks for your help