Solved

Calculating time over 24 hrs

Posted on 2012-03-16
9
184 Views
Last Modified: 2012-03-19
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!!
0
Comment
Question by:russell12
9 Comments
 
LVL 26

Expert Comment

by:tigin44
Comment Utility
try this

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

Expert Comment

by:Gustav Brock
Comment Utility
You can use this old trick:

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

Then format datTime as you like.

/gustav
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
CD ... howdy!
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
Welcome back!

/gustav
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 2

Author Comment

by:russell12
Comment Utility
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
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
Comment Utility
Sorry, don't know what you are doing, but

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

does return 01:45

/gustav
0
 
LVL 2

Author Comment

by:russell12
Comment Utility
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!!!
0
 
LVL 2

Author Closing Comment

by:russell12
Comment Utility
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
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
Ah, that explains.

You are welcome!

/gustav
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now