Excel/access issues

[dantheram]

Guys,

Im trying to link an excel sheet into access via the link table function.

I have a column which contains a sum where the product is displayed in hours.

e.g. 01/05/2011 13:00 - 01/05/2011 12:00 = 01:00:00 (excel format [H]:mm:ss).

The above would be displayed as 1:00:00 which is correct (apart from it also displays a date, as above.

However, where my dates are over 24hrs apart i.e. the product is 365:09:35. access is displaying a lesser hour count and a date.

The field is in access as a date time field but i cannot change it as it linked, is the a way to fix this so access will display the hours and nothing else?

 

[Ruddles]

You don't give the formula which produces the difference in the date/times. Assuming it's something like =C2+D2-A2-B2, change it to =TEXT(C2+D2-A2-B2,"[h]:mm:ss"), then relink the table.

(I may have misunderstood, in which case it would help if you coul post a handful of examples of the worksheet data, the formula which calculates the difference between the date/times and the values you expect to see in Access.)

 

[dantheram]

Thanks for the reply, the formula is simply date x minus date y where date x is always later than y.

eg =B2-C2

c2= 01/01/2011 12:00

B2= 01/01/2011 13:00

= 01:00:00 (custom format [h]:mm:ss)

 

[Ruddles]

Ok, so =TEXT(B2-C2,"[h]:mm:ss").

Any good?

 

[dantheram]

Spot on!!!

Many thanks!