Calc time across days not recorded (Access 03 SP2)

Topic

New Reply

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?

Reply | Quote

Replies

You could use an expression like this in a query:

Duration: [EndTime]-[StartTime]-([EndTime]<[StartTime])

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.

Reply | Quote

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;

Reply | Quote