Solved

# Calculating time over 24 hrs

Posted on 2012-03-16
184 Views
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.

0
Question by:russell12

LVL 26

Expert Comment

try this

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

LVL 49

Expert Comment

You can use this old trick:

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

Then format datTime as you like.

/gustav
0

LVL 75

Expert Comment

CD ... howdy!
0

LVL 49

Expert Comment

Welcome back!

/gustav
0

LVL 2

Author Comment

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

Gustav Brock earned 500 total points
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

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

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

Ah, that explains.

You are welcome!

/gustav
0

## Featured Post

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.