Converting to hours:minutes

[pnad]

Hello all,

We use a vendor application that uses a FoxPro DB that stores 5 and 1/2 hours as 5.50. Basically, if an employee works 5 and 1/2 hours, it stores it in the DB as 5.50. I need to display that information on a Crystal Report v10 as 5:30.

I am using the below formula to display the hours worked on Sunday as is:

if WeekDay({schedbook.sbk_date}) = 1 then {schedbook.sbk_hours} else 0.00

 

[synapsevampire]

Doesn't make sense that it would work on Sunday.

Anyway, try:

whileprintingrecords;
numbervar MyHours:= int({table.field});
numbervar MyMinutes := remainder({table.field},MyHours);
Time(MyHours,MyMinutes,0)

Now you hsve a time type, and you can right click it and format it however you'd like, plus use time functions against it.

-k

 

[lbass]

Hmmm. Shouldn't that be:

whileprintingrecords;
numbervar MyHours:= int({table.field});
numbervar MyMinutes := remainder({table.field},MyHours)*60;
Time(MyHours,MyMinutes,0)

-LB

 

[pnad]

The formula gave me a 'division by zero' error.

Currently, the number of hours worked on the report shows up as 2.00 or 3.00 etc. for a given employee for a given week.If the employee worked 5 and 1/2 hours, it shows up as 5.50 - I want to be able to appear as 5:30.

It is a report that displays hours worked by an employee for a week in the following format:

Name Sun Mon Tue Wed Thu Fri Sat Total Hours Worked

 

[lbass]

Try:

whileprintingrecords;
numbervar MyHours:= int({table.field});
numbervar MyMinutes := ({table.field}-MyHours)*60;

if MyHours = 0 then
"00:"+totext(MyMinutes,0,"")+":00" else
totext(Time(MyHours,MyMinutes,0),"hh:mm:ss")

-LB

 

[pnad]

I apologize. I guess I am not being clear enough.

The package is a scheduling software that is used to schedule employees. I need to generate a report to be submitted to the payroll dept. showing the numbers of hours worked by a given employee for a given week.

I have a report that displays data in the following manner currently:

Name weekOf TotalHrs Sun Mon Tue Wed Thu Fri Sat

ABC 6/19/2006 47.80 8.00 10.75 10.75 8.30 8.00

The DB stores the number of hours worked by an employee in number format such as 8 hrs for 6/23/2006 etc. So for the week of 6/19, I use the weekday formula to determine whether day is sun, mon, tue etc to display the number of hours for the given day. This is in the detail section. I hide the detail section and summarize the data in the group footer section so I get data as shown above. But as you can see, the total is wrong and it is totally misleading and confusing for the payroll department. So I want to be able to display 10.75 as 10h45m etc. and then the total should add the hours and minutes giving a meaningful total.

Hope I am clear this time ! Thanks for all your help so far.

 

[synapsevampire]

LB's formula is correct, I omitted the *60.

Once it is in the proper time format, right click it and select format field and have it display however you'd like, or just use:

whileprintingrecords;
numbervar MyHours:= int({table.field});
numbervar MyMinutes := ({table.field}-MyHours)*60;
timevar MyTime:= time(MyHours,MyMinutes,0);
if {table.field} = 0 then
"0h0m"
else
totext(hour(MyTime),0,"") & "h" & totext(minute(MyTime),0,"") & "m"

-k

 

[synapsevampire]

btw, substitute your sum({table.field},{table.name}) for the {table.field}

-k

 

[pnad]

Thank you for all your help. I am using the below formula and it prints out the time in hh:mm format just fine. How do I total the number of hours now? And also, how can I include a weekday criteria. In my original report, I was using the weekday formula to determing whether the given day was Mon, Tue, Wed etc. based on a start date supplied as an input parameter.

numbervar MyHours;
numbervar MyMinutes;

whileprintingrecords;


MyHours:= truncate({schedbook.sbk_hours});
MyMinutes := ({schedbook.sbk_hours}-MyHours)*60;
timevar MyTime:= time(MyHours,MyMinutes,0);
if {schedbook.sbk_hours} = 0 then "0h0m"
else totext(hour(MyTime),0,"") & "h" & totext(minute(MyTime),0,"") & "m"

Thanks again !

 

[TechiUser]

Hi,

To total the number of hours:
Use the formula only for displaying on the report, but use actual database fields for adding the number of hours. Then apply the same formula on the total and display that.

In your example, you would get 45.8 as the total, then converting it to hrs and minutes you get 45Hrs 48 minutes.

You can still include the weekday criteria similar to before using the variable MyTime
TimeVar MyTime;
if WeekDay({schedbook.sbk_date}) = 1 then MyTime else 0.00

You also need to make sure that you are evaluating your formulae in correct order by using the EvaluateAfter() function.

-PB