1. Registered User
Join Date
Oct 2003
Posts
4

HI,

I've been working on this problem for what seems a life time.

I have a simple table that requires a start time, finish time and total hours.

However the problems I'm having is that the hours can run from one day to the next. so a simple formula to take one from the other would not work.

Does anybody know a formula I can use to do this calculation, because I have tried all sorts and not being very experienced with the use of Access I'm getting myself more into a mess.

2. Registered User
Join Date
Oct 2003
Location
Roanoke, Va
Posts
445

## Re: Calculating Total Hours

Originally posted by paulfish
HI,

I've been working on this problem for what seems a life time.

I have a simple table that requires a start time, finish time and total hours.

However the problems I'm having is that the hours can run from one day to the next. so a simple formula to take one from the other would not work.

Does anybody know a formula I can use to do this calculation, because I have tried all sorts and not being very experienced with the use of Access I'm getting myself more into a mess.

Use the DateDiff function in a query. Ex. TotalHours:DateDiff("h",

StartDateandTime, EndDateandTime). The "h" calculates the difference

in hours. I tried it and it worked for me.

Hope this helps.

Gregg

3. Registered User
Join Date
Oct 2003
Posts
4

## Re: Calculating Total Hours

Originally posted by basicmek
Use the DateDiff function in a query. Ex. TotalHoursateDiff("h",

StartDateandTime, EndDateandTime). The "h" calculates the difference

in hours. I tried it and it worked for me.

Hope this helps.

Gregg
Hi Gregg,

Thanks for your reply I have got this to work, however it only seems to work if my fields are set to general dates, do you know if there is a way of making this work should I use seperate fields for both date and time. I have tried doing this but with no success.

Thanks again for your time in helping me out.

Regards

Paul

4. Grand Poobah
Join Date
Sep 2003
Location
MI
Posts
3,713
If you use separate fileds for dates and times why not concat them together and use CDate to return a timestamp for your hours difference using DateDiff ...

5. Registered User
Join Date
Oct 2003
Posts
4
Originally posted by M Owen
If you use separate fileds for dates and times why not concat them together and use CDate to return a timestamp for your hours difference using DateDiff ...
Hi

Thanks for the reply. I have sorted the issue of dates and times now my only proble is when using datediff it only gives me the hours not the minutes as well. I have tried putting in an "m" value with not success is it possible as the help files give me no help on what values i can use within the function.

Thanks

Paul.

6. Grand Poobah
Join Date
Sep 2003
Location
MI
Posts
3,713
Paul,

Go down to the minutes and then do the calculation yourself ... DateDiff using minutes returns the minutes between the 2 dates ... ALL of them. So just div by 60 to get your hours and the remainder is just that ...

7. Registered User
Join Date
Oct 2003
Posts
4
Originally posted by M Owen
Paul,

Go down to the minutes and then do the calculation yourself ... DateDiff using minutes returns the minutes between the 2 dates ... ALL of them. So just div by 60 to get your hours and the remainder is just that ...
Hi