Question how to calculate time (1 Viewer)

[james7705]

hi
i have 3x time fields in a table i.e time received, time started, time completed
i need to accurately caculate the time between time received and time completed.
whenever the time goes past 00:00 it seems to add 24 hrs to my calculated time
is there a macro or code that i can use to rectify this problem?
thks

rgds

 

[ypma]

I would use the DateDIff function , there are some post already on this forum and all so examples on Google


hours: DateDiff("h", [StartDateTime], [EndDateTime])

Hope this of use to you

 

[Brianwarnock]

Datediff will produce negative hours if the times go overnight.

James does not really provide enough info even how he is currently doing the calculation.

We must assume that as there is no date involved that all differences are less than 24 hours but can go overnight.

Try

CDate(iif(rtime<ctime,ctime-rtime,(#23:00:00#+#01:00:00#)-(ctime-rtime)))

Rtime is received time and ctime is completed time.

The 23+1 is because you cannot code#24:00:00#

Brian

 

[jdraw]

I agree with Brian and would suggest to James -- use a General Date that includes Time in your fields. Access functions will deal with the after
midnight issue if you use General Date. Separating Time from a Date may seem to be some sort of efficiency, but I think you are finding it isn't so.

If this is a new project/database, then changing to General Date may be your best option.

Good luck with whatever you decide.

 

[james7705]

Hi
Sorry about the lack of info.
I have a number of fields...
Start date
Completion date...
These 2x fields are "short date"
Then I have 3x time fields...
Time received
Time started
Time completed...
I'm using a calculated field at the moment between time received and time completed.
Most of the time the timed don't run overnight, but there are times when it does and that's when I have a problem.
The calculated fuction on the field type setting doesn't calculate overnight times.
It is an existing database, been running since July 2012

 

[jdraw]

If Brian's code suggestion works for you then use it.
I think it is a bit of a design issue, that could be corrected by using General Date. However, you are in the operational situation and are in the best spot to determine the cost of each option, and the option to choose.

Good luck.

 

[gemma-the-husky]

the problem with "access" time, is that the time consists of a daycounter plus time - 23 hrs plus 2hrs is actually 1 day, 1hour - but if you format for time, then all you see is the 1 hour.

so knowing this, you can easily manipulate the number that represents the "total time" to obtain the result you want.