DateDiff function giving results with numbers way too large

[joeyo34]

I am finishing up a database that logs basic activities/tasks for my coworkers. As they go in to the database and log an activity in it stays open until they end their day or they start a new task. All this works fine, but the problem comes when I try to determine the total time spent (in hours) on each activity/task. I have written similar queries before and they worked just fine, but for some reason I am having trouble getting this one to work. Here is the expression I'm using...

Time Diff (in hrs): DateDiff("h",[Sample_TM_Table_1]![Time_In],[Sample_TM_Table_1]![Time_Out])

Unfortunately, say Time_In = 8:32:38 am and Time_Out = 8:33:03 am
The expression says that is equal to 946632.

For another Time_In = 8:33:00 am and Time_Out = 3:18:19 PM.
The expression says this equals 946639.

I have tried changing the format of the date/time in both Time_In and Time_Out fields to ensure they were the same and cross checked the expression but still get the same answers each time. Even when I try to use "n" for the interval or "s" it is still highly incorrect. The only thing that makes me think I still have a formatting issue is that the Time_In shows a long date on the results table while Time_Out shows it in a general date format. However, when I go into both the table and the queries to check the formatting they both show general date. Any ideas? I'm all out! Thanks in advance.

 

[Jon K]

I think your Time_In field contains only the time value while your Time_Out field contains the date 27/12/2007 as well as the time. That is why your expression returns the two large numbers.

You can use the TimeValue() function to extract the time value from the Time_Out field for calculation if each pair of time_in and time_out happens on the same day.

If a pair may happen on two different days, you need to store the date in the Time_In field too. And in that case you don't need to use the TimeValue() function.

But still you can't use DateDiff("h") to return the correct number of hours as it only compares the two hours from the two fields and ignores the minutes and seconds.


In fact date/time fields are internally double precision numbers in Days and there are 24 hours in a day, so you can use the following expression to calculate the number of hours to 2 decimal places.-

Round((TimeValue([Sample_TM_Table_1]![Time_Out])-[Sample_TM_Table_1]![Time_In])*24,2)

And if the date is also stored in the Time_In field, you can remove the TimeValue() function from the above expression.

See the query in the attached database.
.

 

[joeyo34]

Apparently that was the problem Jon! I used the code you suggested and it worked perfectly. I am not sure why the formatting did not hold true for both values but I suppose it is really irrelevant now. The good news is all times occur on the same day and if they do not then they are irrelevant anyhow. Thanks a lot for your help, it is much appreciated!!!