|
-
Oct 29th, 2009, 09:44 AM
#1
Thread Starter
Junior Member
How to calculate duration of time using crystal reports? PLS HELP
I am using Crystal Reports Basic for Visual Studio 2008 and backed by SQL Server as my database.
My application is a Employee Punch Card System that will insert each transaction into database. In database, i have these tables: Dateandtime, EmployeeName & Transtype
Note: Each transaction type is assigned a different number.
For example:
Employee Clocks in = 1
Employee Clocks Out = 2
When i generate report, it displays all transaction in order of the dateandtime field.
Now my problem is: How to calculate and display the total number of hours worked by the employee?
( transtype 2 subtract transtype 1 = total number of hours worked )
-
Oct 29th, 2009, 12:22 PM
#2
Re: How to calculate duration of time using crystal reports? PLS HELP
This is not the solution to your problem, but it will help you
How do you create a formula in Crystal Reports (CR) that adds more than
one time field and displays the total in the same format: hh:mm:ss?
For example:
1:45:01 + 1:45:01 should display 03:30:02 and not 2:90:02
Solution
In order to get the sum of more than one time field and to display the
total of the time field in the same format (hh:mm:ss) you must complete
the following steps:
A. Convert all the time fields to a common time unit such as seconds
B. Calculate the total time in seconds
C. Convert the seconds back to hh:mm:ss format.
A.-Convert time fields to seconds
--------------------------------
Convert the time fields to seconds so that a common time unit is used to
sum up all three different time units.
If your field is a datetime field, complete the following steps:
1. Create a new formula and call it @ConvertTimeToSeconds
2. To convert the datetime field to seconds, create a formula similar to
the following:
Code:
local numbervar hours;
local numbervar minutes;
local numbervar seconds;
// Convert the hours to seconds by multiplying by 3600
hours := hour({@time}) * 3600;
// Convert the minutes to seconds by multiplying by 60
minutes := minute({@time}) * 60;
seconds := second({@time});
//add up all the seconds
hours + minutes + seconds;
If your field is string field with the format of hh:mm:ss to convert to
seconds complete the following steps:
1. Create a new formula and call it @ConvertTimeStringToSeconds
2. Create a formula similar to the following to convert the string field
to seconds:
Code:
local numbervar hours;
local numbervar minutes;
local numbervar seconds;
// Parse out the hours portion of the string and multiply by 3600 to convert to seconds
hours := tonumber({timestringfield}[1 to 2])* 3600;
// Parse out the minutes portion of the string and multiply by 60 to convert to seconds
minutes := tonumber({timestringfield}[4 to 5]) * 60;
// Parse out the seconds
seconds := tonumber({timestringfield}[7 to 8]);
// Add up all the seconds
hours + minutes + seconds;
B.-Calculate the total time in seconds
------------------------------------
Create a summary formula that will sum the
@ConvertTimeToSeconds or @ConvertTimeStringToSeconds
1. Create a new formula and call it @TotalSeconds
2. To sum up either formula, create a formula similar to the following:
[Code]
sum(@ConvertTimeToSeconds, Group)
- OR -
sum(@ConvertTimeStringToSeconds, Group)
C.-Convert the seconds back to hh:mm:ss format.
--------------------------------------------
Create a formula that converts the @TotalSeconds results back to
hh:mm:ss format:
1. Create a new formula and call it @ConvertTotalSeconds
2. To convert the results from @TotalSeconds back to hh:mm:ss format,
create a formula similar to the following:
Code:
local numbervar RemainingSeconds;
local numbervar Hours ;
local numbervar Minutes;
local numbervar Seconds;
//divide the @TotalSeconds by 3600 to calculate hours.
//Use truncate to remove the decimal portion.
Hours := truncate({@TotalSeconds} / 3600);
// Subtract the hours portion to get RemainingSeconds
RemainingSeconds := {@TotalSeconds} - (Hours * / 3600);
// Divide RemainingSeconds by 60 to get minutes.
//Use truncate to remove the decimal portion.
Minutes := truncate(RemainingSeconds/60);
// Subtract the Hours and Minutes and what is left over is seconds.
Seconds := {@Totalseconds} - (Hours * 3600) - (Minutes * 60);
// Format the hours, minutes, and seconds to hh:mm:ss
totext(Hours,"00") + ":" + totext(Minutes,"00") + ":" + totext(Seconds,"00")
JG
-
Nov 2nd, 2009, 02:59 AM
#3
Thread Starter
Junior Member
Re: How to calculate duration of time using crystal reports? PLS HELP
thanks for the effort trying to solve this question. but im struggling to get it right...
-
Nov 2nd, 2009, 12:38 PM
#4
Re: How to calculate duration of time using crystal reports? PLS HELP
Also, You can try the next function (check out CR help for details)
Code:
DateDiff("h", {datetime1}, {datetime2})
-
Nov 16th, 2009, 06:39 AM
#5
New Member
Re: How to calculate duration of time using crystal reports? PLS HELP
Hi,
I'm doing something similar. Except in my table it will create a line for each "Swipe/Tag" as In or Out.
Now what if there were multiple in's and out's. How do I calculate the hours for the day and then per week?
Thanks
Pieter
-
Nov 17th, 2009, 12:49 AM
#6
Thread Starter
Junior Member
Re: How to calculate duration of time using crystal reports? PLS HELP
 Originally Posted by pjjdp
Hi,
I'm doing something similar. Except in my table it will create a line for each "Swipe/Tag" as In or Out.
Now what if there were multiple in's and out's. How do I calculate the hours for the day and then per week?
Thanks
Pieter
thats why im posting the question here.. i also struggle
-
Sep 19th, 2013, 05:29 PM
#7
New Member
Re: How to calculate duration of time using crystal reports? PLS HELP
JGGT - Kudos! Your solutions was beautiful, and works like a champ. I had some varchar fields in hh:mm:ss format that needed to subtracted, and your codes worked beautifully with very little tweaking. I realize that I am responding well after-the-fact, but wanted to give props where due.
Kristina
-
Sep 21st, 2013, 01:14 AM
#8
Re: How to calculate duration of time using crystal reports? PLS HELP
Kristina, I'm glad that it helped You !
JG
... If your problem is fixed don't forget to mark your threads as resolved using the Thread Tools menu ...
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|