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!!
LVL 2
russell12Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tigin44Commented:
try this

 IIf (Departed2 < Arrived1, Departed2 - Arrived1 + 2400, Departed2 - Arrived1)  / 60
Gustav BrockCIOCommented:
You can use this old trick:

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

Then format datTime as you like.

/gustav
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
CD ... howdy!
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Gustav BrockCIOCommented:
Welcome back!

/gustav
russell12Author Commented:
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
Gustav BrockCIOCommented:
Sorry, don't know what you are doing, but

  TimeValue(CDate(#01:19# - #23:34# + 1))

does return 01:45

/gustav

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
russell12Author Commented:
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!!!
russell12Author Commented:
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 BrockCIOCommented:
Ah, that explains.

You are welcome!

/gustav
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.