Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Calc time across days not recorded (Access 03 SP2)

    Is there a way to calculate elasped time while making an assumption on the day? For example, i am trying to calculate how long an EOD process take each day. it begins a 8pm (20:00 as it is reported from a system), let's say last night on 11/29/06. it completed at 1am (01:00) on 11/30/06. The system is not giving date stamp, just short time. The problem is that it does not always completed past midnight. Sometimes the cycle complete earlier on the same day (let's say for example at 10pm (22:00) on 11/29/06.) Therefore, we cannot assume to identify the end time to be the next day. is there a way to calculate the elapsed time without manually updating the actual complete date?
    thanks
    christine

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calc time across days not recorded (Access 03 SP2)

    You could use an expression like this in a query:
    <code>
    Duration: [EndTime]-[StartTime]-([EndTime]<[StartTime])
    </code>
    where StartTime and EndTime are the time fields. You must explicitly set the Format for this field to Short Time (or whatever time format you prefer).

    Explanation: ([EndTime]<[StartTime]) is False = 0 if EndTime is later than StartTime, and True = -1 if EndTime is before StartTime. We may assume that if EndTime is before StartTime, it is actually the next day. In that case, we subtract -1 i.e. add 1. Since times are stored as fractions of 1 day, we are adding 1 day = 24 hours.
    Example:
    StartTime = 18:00 (6 PM), this is stored as 0.75 (18/24 of a day)
    EndTime = 3:00 (3 AM), this is stored as 0.125 (3/24 of a day)
    EndTime - StartTime = 0.125-0.75 = -0.625. But since EndTime < StartTime, we add 1, giving -0.625+1 = 0.375. This corresponds to 0.375 * 24 hours = 9:00. This is the elapsed time.

  3. #3
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Calc time across days not recorded (Access 03 SP2)

    Sorry, I forgot to respond with a confirmation. Your explanation definitely helped! Thanks again.

    SELECT EOD_tbl.database, EOD_tbl.date, EOD_tbl.start_time, IIf(([database])=9,[date],IIf([end_time]>=#12/30/1899 20:0:0#,IIf([end_time]<=#12/30/1899 23:59:0#,[date],[date]+1),[date]+1)) AS end_dt, EOD_tbl.end_time, [End_Time]-[Start_time]-([End_Time]<[Start_Time]) AS Dura, Month([date]) AS [month], db_tbl.ID
    FROM EOD_tbl INNER JOIN db_tbl ON EOD_tbl.database = db_tbl.ID1;
    thanks
    christine

Posting Permissions

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