Results 1 to 8 of 8
  1. #1
    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
    Attached Files Attached Files

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

    Cool 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.
    Bob

  3. #3
    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. #4
    Join Date
    Dec 2003
    Location
    WA, USA
    Posts
    51

    Cool

    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.
    Bob

  5. #5
    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. #6
    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.
    Bob

  7. #7
    Join Date
    Mar 2003
    Posts
    225
    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

  8. #8
    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.
    taxes

Posting Permissions

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