Time Calculations and Format (1 Viewer)

[FazerLogistics]

I have a subform in which I want to calculate the difference between a start time and finish time, the problem I am having is when the start time is on a different date to the finish time

Start 23:15 on 14th Jan finish 00:015 on 15th Jan, also I want the answer to be the number of minutes. The fields I have are

BatchStartTime Short Time
BatchFinishTime Short Time

BoilTime ([BatchStartTime] - [BatchFinishTime]) General Number.

There are many batches to enter per job and the MainForm has the StartDate as I didn't want to enter the StartDate for each batch. Hope this makes sense.

Thanks

 

[pr2-eugin]

Try the DateDiff function !

BoilTime : DateDiff("n", [BatchStartTime],[BatchFinishTime])

 

[FazerLogistics]

Hi Paul

Thanks for your reply, my calculation works for same day calculations but not for ones crossing over at midnight eg StartTime 23:30 FinishTime 00:15 I want a return value of 45 but I am getting -1395

I put the DateDiff expression in the subform query as follows

BoilTime: DateDiff('n',[BatchStartTime],[BatchFinishTime])

Other query field is StartDate - Date, do I need a FinishDate field in the qury also or am I missing something else.

Thanks

 

[namliam]

Is because your time field doesnt contain a date component....

Try this:
DateDiff('n',[BatchStartTime],Iif([BatchFinishTime]<[BatchStartTime],1+[BatchFinishTime], [BatchFinishTime]))

 

[FazerLogistics]

Worked a treat, Many Thanks