asked on

Calculating time over 24 hrs

I have a program that keeps how many hours worked at a store. I have 2 text boxes one for Enroute to strore (named:Time Arrived1) and Time Completed (Time Departed2). I can get the correct time if it isnt over 24 hrs. example: 2200-2350 = 3.83 but if it is 2334-0119 i get -22.25. When it should be 1.75. I take total time /60. Then text field that holds this value has a control source of =DateDiff("n",[time departed1],[time arrived2])/60.

Any help would be greatly apprectiated. Also I have tried =DateDiff("n",[time departed1]-1-[time arrived2])/60 and it doesnt work. Also the time fields are written in the db as time only. The field is set for time/date and the text field is set for short time.

Thanks in advance!!

tigin44

try this

IIf (Departed2 < Arrived1, Departed2 - Arrived1 + 2400, Departed2 - Arrived1) / 60

Gustav Brock

You can use this old trick:

datTime = TimeValue(CDate(TimeDeparted - TimeArrived + 1))

Then format datTime as you like.

/gustav

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

CD ... howdy!

Gustav Brock

Welcome back!

/gustav

russell12

ASKER

Hey guys, Thanks for the quick reply and sorry I have not had a change to try these ways out until tonight! I have played with both sugestions, and they are both not working.
timediff2 = TimeValue(CDate([Time Departed1] - [Time Arrived2] + 1))

It is returning a value of time. I have formated and put an input mask on this and it is still not doing the trick and it is also returning (10:15:00 PM) when it needs to return 1:45. because the 10:15 would translate to 10.25 hrs when in actuality it is 1.75 hrs. The [time departed1] is 2334 and the [time arrived 2] is 0119.

=IIf([time departed1]<[time arrived2],[time Departed1]-[time Arrived2]+2400,[time departed1]-[time arrived2])

It is returning 0.927083333333333 with the same times. Any help would be greatly appreciated and again I am sorry for not replying in a timely manor, just been very busy with work!!
Thanks

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

russell12

ASKER

I see your point, and yes it does work that way but for some reason when it is trying to pull from the textboxes it is returning 10:15 P.M. I am going to keep playing with this, it has to be a problem with the textbox/textboxes. I have the format on the text boxes set to short date, 24hr. and in the db i have it set to time/date but it only is storing the time. I will keep playing with this, if you have any sugestions please express them.

Thanks!!!

russell12

ASKER

TimeValue(CDate([Time Arrived2] - [Time Departed1] + 1))

This was the answer, knew it was something simple. I was using TimeValue(CDate([Time Departed1] - [Time Arrived2] + 1)). After looking over your answer again, I realized i was subtracting wrong!! Thanks for your QUICK response and your help!!!!!!!!!

Russell

Gustav Brock

Ah, that explains.

You are welcome!

/gustav