# Thread: Interesting Time Calculation Problem

1. Registered User
Join Date
Mar 2003
Posts
225

## Unanswered: Interesting Time Calculation Problem

i need to calculate time, the problem i have is that we are trying measure production over the day, however our days run from

22:00 through to 22:00 the following evening

i have tried to calculate it by taking the end time from away the start time, but when i tried to do this it gives the wrong figure

eg

DayCode - 3111 (This is just the code we use to represent each day)
Line - C3 ( Machine identification)
Start Time - 22:00 ( Time the Machine started)
End Time - 18:00 (time the machine ends)
Duration - 04:00 (the figure that is display when calculated in access)

the duration figure should be 20:00 hours

i have managed to get it to work upto a point with the following code

runtime: IIf([starttime]=[endtime] And [starttime]=#22:00:00#,#23:59:59#,IIf([starttime]>[endtime],(#23:59:59#-[starttime])+[endtime],[endtime]-[starttime]))

but

if the the two times are the same for example 22:00 on the first evening through til 22:00 the following evening, i should get a result of 24 hours but instead i get zero, so i was wondering if there is a work around to this problem, i also need the time to be displayed in full hours whereas the above code only dislpays it to the correct hour minus 1 second.

i have attached a sample for you to look at:

i hope this makes a little sense.

thanks

Andy

2. Registered User
Join Date
Dec 2003
Location
WA, USA
Posts
51

## Re: Interesting Time Calculation Problem

Andy, try changing your start and end formats from short time to General date. Then use the Datediff function like this:
DateDiff("h",[Table1]![Starttime],[Table1]![Endtime])

This by setting the interval to Hours you will get an integer result of the difference in hours.

I hope this helps.

3. Registered User
Join Date
Mar 2003
Posts
225
i have tried what you suggested but still not getting the answer i am after, when i try it the record that shows 22:00 start to end 22:00 = zero minutes, but this should be 24 hours.

4. Registered User
Join Date
Dec 2003
Location
WA, USA
Posts
51
The primary reason for using a general date is to differentiate between days. Meaning that you need to be looking for the difference between 10:00 yesterday and 10:00 today.
If you use the same date for both Start and end and they are both 10:00 your result should be 0.

5. Registered User
Join Date
Mar 2003
Posts
225
ok thanks, but the way my stupid company work is

daycode 4001 is January 1st
daycode 4002 is January 2nd etc

but daycode 4002 would start at 22:00 on daycode 4001 (does his makes any sense)

so i have a table that indicates

dates and daycodes

but on the data entry side i am required to just enter a daycode and the planned time for that day,

for example

daycode - 4003 - (which is actually the tail end of Daycode 4002, in real terms)
Start - 22:00
End - 22:00 - (which is actually the tail end of Daycode 4003, in real terms)
Total planned production time is 24 hours.

can you see how this works, and also how stupid it is. the reason that they do it this way is all to do with shift patterns.

but i need to make it as simple as possible for the data inputters to use, thats why i want them to have to just enter the daycode and also start and end times.

i have seen this done on other programs so i know it can be done, but aint got a clue how to do it.

6. Registered User
Join Date
Dec 2003
Location
WA, USA
Posts
51
It sounds to like the easiest way to handel this would be validation code on the back side of the data entry form that converts the datecode and time entered by your user into a general date format to be storred in your table. I'm not sure from what you have told me if this is possible or not.

7. Registered User
Join Date
Mar 2003
Posts
225

http://www.fabalou.com/Access/General/DatesAndTimes.asp

and the following calc seems to do what i am after

IIf([StartTime]<[EndTime],DateDiff("h",[StartTime],[EndTime]),24-DateDiff("h",[EndTime],[StartTime]))

i now need to take this one step further......

i now need to calculate, using the same principles as above but this time i need it to calculate the runtime between,

22:00 and 06:00
06:00 and 14:00
14:00 and 22:00

i was hoping someone could help me to modify the above to do this.

cheers again

Andy

8. Registered User
Join Date
Dec 2003
Location
Bristol, England
Posts
29

## Re: Interesting Time Calculation Problem

Hi

I am not familiar with VBA but how about using the code for:

if startime>=endtime then
days=1
else
days =0
endif
?????
if endday-startday>1 then
days=days+endday-startday-1
endif
?????
hours=24*days+(endtime-starttime)

If you do not have to cover the starttime and endtime covering more than 24 hours, omit the code between ?????

A check should be made to ensure the operator does not enter the same day & time for both start and end items.

If you have to cover a change of year then an additional check is necessary.
Last edited by taxes; 01-14-04 at 09:00.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•