[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Calculating time over 24 hrs

Posted on 2012-03-16
9
Medium Priority
?
213 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
ID: 37732148
try this

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

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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 53

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 53

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 53

Expert Comment

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

You are welcome!

/gustav
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

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

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

612 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