Time calculation

[Chimp8471]

i needto calculate time, the problem ihave 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

Any ideas how i can overcome this please

Andy

 

[CJBIRKIN]

Hi

The data you have shown suggestes that the machine runs only for only 1 of your days 3111. The answer i am giving you could be modified for runs greater than one of your days

Here's an example:

first time 22:00 date 01/01/2003 second time 18:00 02/01/2003

The logic you need is:

if first time is > then the second time then 24:00-first time

Explanation: if the start time is > than the end time the start time must be on the previous day(01/01/2003). So 24 - the start time would give you 2 hours.

The next part would be to add the end time to the result of this calculation.

(24:00-22:00)+18:00 = 20hrs


Secondly:

If the previous statment is false then the 2 times must be on the same day i.e 00:01 to 22:00 on the 02/01/2003 so you can just take the start time from the end time.


The final logical statement would look like

if start time>end time then
(24:00-start time)+end time =hrs
else
end time - start time = hrs
end if

Hope that helps

Chris

 

[Chimp8471]

thanks, but i should have explaind that my coding skills are very poor, where should i enter this info please

 

[CJBIRKIN]

Hi

Are you doing this in a query?

Chris

 

[Chimp8471]

was going to yes, unless you can explain an easier way for me to do it....but query probably the easiest for me to cope with

 

[CJBIRKIN]

Hi

I assumed from your question that you had already had some sort of query as you said that the calculation gave you the wrong answer.

what i would do is in a new column type (where you would normally put a field name);

Runtime: iif([start time] > [end time], (#23:59:59# - [starttime])+[end time],[end time]-[start time])


The reason for 23:59:59 is that i have found problems using 24:00.

When you run the query you should get a column called Runtime containing the results of the calculation.

Chris

 

[Chimp8471]

works a treat thanks

 

[CJBIRKIN]

no worries

 

[neileg]

The formula will fail if the start time and the end time are equal!

Try this:
Runtime: iif([end time] < [start time], (#23:59:59# - [starttime])+[end time],[end time]-[start time])

 

[CJBIRKIN]

Hi Neil



I'm not sure how your answer resolves the problem you suggest. -by using endtime<starttime rather than starttime > endtime, - any more than mine does.
The fact is that if both times are equal, then the answer is 0.

Therefore the first part of my formula -and yours- evaluates to false. But the second part evaluates to true giving the correct answer in both cases.

i.e starttime 1:00 endtime 1:00

if starttime > endtime (false) or endtime<starttime(false)


else

endtime - starttime = 0

end if

Chris

 

[neileg]

Yes Chris, I know what you mean, but Andy suggested that the day can run from 22:00 to 22:00, ie 24 hours. So evaluating 22:00-22:00 to be zero, is the wrong answer!

Of course, my solution was rubbish, but I stick by my identification of the problem.

You seem to have solved Andy's problem, so I'll leave it, as it's a busy day here!

 

[CJBIRKIN]

Fair point well made!!

If anyone's still interested i've tested this formula and it seems to work.

runtime: IIf([start time]=[end time] And [start time]=#22:00:00#,#23:59:59#,IIf([start time]>[end time],(#23:59:59#-[start time])+[end time],[end time]-[start time]))

to take into account the possibility of a 24 hr run time 22:00 to 22:00.

Chris

 

[Chimp8471]

many thanks, this is great, really haven't had time to check this, as i am trying to sort out my other problem

link below

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=49147

can you help:

i need to try and sort this in time for me to go to college at 1:00pm this afternoon

cheers


Andy

 

[Chimp8471]

right i posted this some time ago and i thought that the problem had disappeared but it has shown it's ugly head again, i think i have sorted it though but just want to see if this is possible.

following through the calculation, this works a treat, but as stated above i will effectivly lose a second on each calculation.

is there a way to round thess up to the following

Daycode Starttime Endtime runtime required display
1001 22:00 01:00 02:59:59 03:00:00
1002 22:00 21:59 23:58:59 23:59:00
1003 22:00 22:00 23:59:59 24:00:00


cheers


Andy

 

[Mile-O]

DateAdd("s", 1, [MyField])

 

[namliam]

might it be the age old mathmatical calculation problem?

The question: "How many days have passed since 01-01-2004?"
Today = 13 jan so we say 13.....
Mathmatics say 12, 13 minus 1 = 12 right?

so mostly we do (13-jan minus 01-jan) PLUS 1 or stuff like that.
Try simply doing your calculation and adding 1 second:
Calc + #00:00:01# or as Mile would say, use DateAdd()

Regards

 

[Chimp8471]

cheers milo, but you aint getting off that lightly

this works great

but

as i think you have previously stated this will be ok unless the two times are the same, so i was wondering if there is a work around to this problem,

if the two times match, it will always mean that this will equal 24 hours and not zero mins.


i have attached a sample for you to look at:

thanks

Andy

 

[Chimp8471]

thanks everyone for your help.....

i just found this article on the net.

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

 

[Mile-O]

Whay are you just storing the time and not the date and time?

If you store both these values in one field then it's a very simple operation. It could also eliminate the DayCode thing as that's easily calculable from the Date.

 

[Chimp8471]

it's all to do with the data entry method, i am trying to re-write the current DB that we have, and this is a major issue here. and i need to make it as quick as possible for the inputter to do, so if they can just enter a daycode then a start time and end time relevant to that daycode, i believe (although happy to listen to other suggestions) this is the best way to do this.

the current DB has :

a table called - tbl_Dates which has the following fields:

DayCode
UKDate
Day
WeekNumber
PeriodNumber

and this is linked to a table tbl_production

DayCode
Line
PlannedTime
Impressions - production terms
StdPack - production terms
Output - production terms

these two are then joined via the Daycode.

where i work they tend to use the Daycodes as opposed to the actual date to reference things (bit odd i know)

I am not sure if i have really answered your question though.

Andy