Click here to Skip to main content
14,639,066 members
Rate this:
Please Sign up or sign in to vote.
Hi Good day to all developers here.

First thing i would like to know is how can i calculate the number of work hours. I am using VB.Net 2008, MySQL as my Back end and Crystal Reports of .Net for Attendance reporting purposes

Assuming We have a database fields of Name, Date, Time_in & Time_out
and the values are,

Name: Joshua
Date: 5/17/2012
Time_in: 1:30 AM
Time_out: 5:30 AM

Expected Result Should Be: 4

I managed to display the name, Date, Time_in and Time_out in Crystal reports however i do not really know how to calculate the work hours i tried formula fields and dateandtime functions but it doesn't work for me.

Can anyone give me a hand for me to have a good start

Updated 16-May-12 7:44am
Rate this:
Please Sign up or sign in to vote.

Solution 1

Crystal should support the DateDiff function so you could try to set up the formula like
DateDiff ("h", {Time_in}, {Time_out})
VJ Reddy 16-May-12 19:58pm
Succinct and to the point. 5!
Wendelius 17-May-12 4:26am
Thanks :)
JMAM 16-May-12 20:36pm

First of all i would like to thank you for taking a time answering this post.

i tried

DateDiff ("h", {Time_in}, {Time_out})

It works, but when Time_in starts at PM and Time_out is at AM
the out gives me a negative values
Example when user have a graveyard shifts

Time_in 8:00 PM
Time_out 3:00 AM

Result would be -17
Wendelius 17-May-12 4:27am
Yes, that's correct if you have only time portion in the variable. If the variables would include the date, the result should be correct. For example:

Time_in 5/16/2012 8:00 PM
Time_out 5/17/2012 3:00 AM
JMAM 17-May-12 8:16am
Thanks once again but sir i am using MySQL and the values that i have in the database is

Time_in : 2012-05-17 20:31:08
Time_out : 2012-05-17 03:10:16
and i did exactly what you said but the result was "-17"

Thanks for sharing your knowledge and time
Wendelius 17-May-12 8:34am
That's odd. And you're sure that despite of the name of the variable (Time_in) it really contains the date also. To check this you can for example put a field on the report to show the content directly.

If that still doesn't work, what if you calculate the date difference in MySql when you query the data instead of calculating it on the report?
Myke Ruiz 25-Sep-16 2:32am
there is something wrong in your date.
time in:2012-05-17 20:31:08
Time out: 2012-05-17 03:10:16
the time out should be 2012-05-18 03:10:16
Member 13111995 10-May-17 8:23am
plz send me code
Time_in : 2012-05-17 20:31:08
Time_out : 2012-05-17 03:10:16

JMAM 17-May-12 8:47am
Yes I have tried you mas see the screen shot of the report output here @

What i am trying to do is create a simple time @ attendance system that will display In's and Out's of the user plus the calculated hours of work. the formula you have provided works for the two rows at the screen shots.

Also i am not that good in SQL statement using that query and to generate an output file i think crystal report would make the jobe easy am i right or i am missing the point?

Wendelius 17-May-12 8:54am
You're right that it would can easier to handle the calculation in Crystal.

However, based on the screenshot, even the first two rows seem to be wrong. Shouldn't the first row have 7 and the second one 5, now the are the opposite way???

Have you tried any other intervals, such as day or second? Also have you tried changing the date parameters vice versa?
JMAM 17-May-12 9:01am
I think sir the first and second row is correct please have a look again.

5/17/2012 8:27:14AM - 5/17/2012 1:27:17PM = 5
5/17/2012 8:27:34AM - 5/17/2012 3:27:37PM = 7

Sir i you have a simple sample of this kind of prototype can you attache it also i can send you the prototype project if you want
Wendelius 17-May-12 9:28am
Sorry, my bad, they are absolutely correct :)

Actually I don't have any prototype at hand. but I don't think that matters.

Have a look at the dates. Isn't -17 actually correct? I think the problem is in your data. Shouldn't the last Time_out be

5/18/2012 3:10:16AM

now it is

5/17/2012 3:10:16AM

so the time out is actually earlier than time in...
JMAM 17-May-12 10:10am
Wow thanks i didn't notice this. it works :)
One last thing sir how can i calculate exact work hours

i mean if a user time_in in at 5/17/2012 1:00 PM and Time_out 5/17/2012 3:30 PM then the result should be "2.5"
Wendelius 17-May-12 10:16am
I think the easiest way is that you use minute as interval. The if you like, you can format it in the report by dividing the amount of minutes with 60 :)
JMAM 17-May-12 10:22am
I think every thing that you said works perfectly and its all i need can you give me an example of minutes interval like providing me this example below.

DateDiff ("h", {Time_in}, {Time_out})

I do not know minutes interval in crystal reports thanks to your quick, bright and generous information :)
Wendelius 17-May-12 10:32am
Just replace h with n :)
DateDiff ("n", {Time_in}, {Time_out})
JMAM 17-May-12 10:39am
Well what can i say it all works, thanks a lot :)
Wendelius 17-May-12 14:06pm
You're welcome :)
Rate this:
Please Sign up or sign in to vote.

Solution 2

If put the time in and time out into DateTime instances in VB.Net, you can just subtract them.
JMAM 16-May-12 20:43pm
Hi thanks for the reply however i do know how to do this can you give me a sample code using Time_in and Time_out

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100