?
Solved

Calculating time over 24 hrs

Posted on 2012-03-16
9
Medium Priority
?
200 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 26

Expert Comment

by:tigin44
ID: 37732148
try this

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

Expert Comment

by:Gustav Brock
ID: 37732152
You can use this old trick:

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

Then format datTime as you like.

/gustav
0
 
LVL 75
ID: 37733331
CD ... howdy!
0
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
LVL 51

Expert Comment

by:Gustav Brock
ID: 37733538
Welcome back!

/gustav
0
 
LVL 2

Author Comment

by:russell12
ID: 37736530
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 51

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 37736550
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
ID: 37736596
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
ID: 37736601
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 51

Expert Comment

by:Gustav Brock
ID: 37736617
Ah, that explains.

You are welcome!

/gustav
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

752 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