Need Help with timecard formulas and Header 1

[fabianor]

CR 8.5

I can email what I have. NEED HELP BADLY

I have a Crystal Report saved with data and an Excel sheet as a sample of what I want to achieve.

The report is now showing the total hours worked per week and the in and out times and dates per work week.

I can not seem to find a way to get the date header to appear above the second week. The date range from 7/4/04 to 7/10/04 appears for the first week but 7/11/04 to 7/17/04 does not appear above the hours worked for the second week.

The only other item that I would like to address is the total hours worked for the week.

Regular hours for the week are hours to up 44 hours per week and Overtime hours is the balance of Total hours over 44 hours if applicable or else Overtime hours is 0.

I really appreciate any help or guidance or changes to my report that you may provide since I do not use Crystal Report regularly but learn from examples.

Thanks again.

 

[lbass]

You need to show a little sample of your current report and a sample of how you would like it to look in this thread. Be sure to clarify your group structure. E-mailing and solving problems outside of the forum are discouraged, in large part because other readers cannot follow and learn from the problem/solution.

Are you looking for a formula for overtime? Try something like:

//{@Overtime}:
if sum({table.hours},{table.date},"weekly") > 44 then sum({table.hours},{table.date},"weekly") - 44

This assumes that your group structure is: Employee (grp #1) and weeks (grp #2).

-LB

 

[fabianor]

Hi LB

Thanks for your reply.

How can I attach a copy of what I have upto now and where I want to go with it in this thread.

This is new to me.

For the most part I think I got what I want but need some guidance to as how to calculate the regular and overtime hours and the summarize it per employee.

I have saved TimeCard with data and pdf file of reports and an excel sheet of what I want the end result to be.

I basically need help to add this to the report. I learn mainly by example. If I see the correct way then I can compare what I had to see where I going wrong.

Your help is greatly appreciated. I have to young child and my wife is on my back for spending to much ime on the computer.

Thank

 

[lbass]

Just show an example of what your data looks like, and explain what your group structure is. From what you've said, it sounds like your data looks like this:

Employee1
7/4/04-7/10/04 31
8
4
8
7
4
7/11/04-7/17/04 22
8
8
6

Employee2
7/4/04-7/10/04 40
8
8
8
8
8
7/11/04-7/17/04 20
8
4
8

Etc.

An example like this is all we need, both for how it looks and how you would like it to look. Are you trying to get the weeks to go across instead of down? This would be a crosstab approach then.

-LB

 

[fabianor]

This is the report DEF FILE

;
; Timecard Report
;

Filename = BI-WEEKLY TIMECARD.RPT
Description = Timecard Report
HelpContextID = 0

GroupCount = 2
TableCount = 2

DefaultSelectionFormula = {@WeekStartDate} in Last4WeeksToSun
DefaultSelectionDescription = Week Starting Date in Last Four Weeks

VisibleDescription = List timecard activities
VisibleSectionName = DETAIL
VisibleSectionName = GH3
VisibleSectionName = GF3

; Description Field Name Type Level Crit. G.T. #
; --------------- --------------------- ---- ----- ------ ------
FIELD = Week Starting Date, {@WeekStartDate}, 2, 2, -1, 0
FIELD = Week Ending Date, {@WeekEndDate}, 2, 0, -1, 0
FIELD = Cashier ID, {Cashier.Number}, 0, 1, -1, 0
FIELD = Cashier Name, {Cashier.Name}, 0, 1, -1, 0
FIELD = Date In, {@DateIn}, 2, 0, -1, 0
FIELD = Date Out, {@DateOut}, 2, 0, -1, 0
FIELD = Hours, {@Hours}, 1, 0, -1, 0;
___________________________________________________________
This is the structure

Report Header
Page Header
Page Header A
Page Header B
Group Header #1 Cashier.Name - A
Group Header #2 @WeekStartDate -() A
Group Header #3 Cashier.Name - A
Details
Group Footer #3 Cashier.Name - A
Group Footer #2 @WeekStartDate -() A
Group Footer #1 Cashier.Name - A
Page Footer
Report Footer
Report Footer A
Report Footer B
___________________________________________________________

This is the report

Bi-Weekly Timecard Report As of: 07/28/2004
Sort Order: 1. Cashier Name
2. Week Starting Date
Selection Criteria: Week Starting Date Equal(=)July 4, 2004 OR
Week Starting Date Equal(=)July 11,2004

Week starting 07/04/2004
Sun 7/4 Mon 7/5 Tue7/6 Wed 7/7 Thur 7/8Fri 7/9Sat7/10Total
1 Administrator
2.00 4.00 2.00 2.00 2.00 2.00 2.00 16.00

Week Ending 07/10/2004
Time In: Time Out: Hours:
07/10/2004 08:00:51 07/10/2004 10:00:51 2.00
07/09/2004 08:00:51 07/09/2004 10:00:51 2.00
07/08/2004 08:00:51 07/08/2004 10:00:51 2.00
07/07/2004 08:00:51 07/07/2004 10:00:51 2.00
07/06/2004 08:00:51 07/06/2004 10:00:51 2.00
07/05/2004 08:00:51 07/05/2004 12:00:51 4.00
07/04/2004 08:00:51 07/04/2004 10:00:51 2.00

*Total Hours Week 1 Reg Hrs 16.00 OT Hrs 0.00


Week Starting 07/11/2004
Sun7/11 Mon7/12 Tue7/13Wed 7/14Thur 7/15Fri7/16Sat7/17 Total
1 Administrator
8.00 8.00 8.00 8.00 8.00 8.00 8.00 56.00

Week Ending 07/17/2004
Time In: Time Out: Hours:
07/11/2004 08:000:00 07/11/2004 04:000:00 8.00
07/12/2004 08:000:00 07/12/2004 04:000:00 8.00
07/13/2004 08:000:00 07/13/2004 04:000:00 8.00
07/14/2004 08:000:00 07/14/2004 04:000:00 8.00
07/15/2004 08:000:00 07/15/2004 04:000:00 8.00
07/16/2004 08:000:00 07/16/2004 04:000:00 8.00
07/17/2004 08:000:00 07/17/2004 04:000:00 8.00

*Total Hours Week 2 Reg Hrs 44.00 OT Hrs 12.00

*Bi-Weekly Hours Total Reg Hrs60.00 OT Hrs 12.00 Total 72.00

Grand Total Hours for Pay Period 72.00


My report work well except for the date range for week two.

Sun7/11 Mon7/12 Tue7/13Wed 7/14Thur 7/15Fri7/16Sat7/17Total (Does not appear above the hour row.)

1 Administrator
8.00 8.00 8.00 8.00 8.00 8.00 8.00 56.00
This Does appear. Hours are there for each employee in second week but dates are not above hours worked.

Then I need to get the Regular Hours and over time hours for each week from Total hours worked each week.

*Total Hours Week 1 Reg Hrs 16.00 OT Hrs 0.00
*Total Hours Week 2 Reg Hrs 44.00 OT Hrs 12.00

And then new a total for both weeks
*Bi-Weekly Hours Total Reg Hrs 60.00 OT Hrs12.00 Total 72.00

The total hours of 72.00 all ready total both weeks.

The Grand Total Hours for Pay Period 72.00 is the total hours worked by all employes for the two weeks and works.

I hope you call follow all of this.

I have the report saved with data if needed with 3 employees.

 

[lbass]

Go to design mode, and see if there is a crosstab in the Group #2 header (week). If there is, right click on a column label->format field->common->suppress and see if there is a formula in the x+2 area. This would probably be:

groupnumber <> 1

Remove the formula. If there is no crosstab, then you probably have a series of formulas there instead. Please share the contents of a couple of those formulas here.

For the regular hours formula, use:

//{@regular} to be placed in the GH#2 header or footer:
if sum({table.hours},{table.date},"weekly") <= 44 then
{table.hours} else 44

For overtime use the formula I suggested earlier:
//{@Overtime} to be placed in the GH#2 header or footer:
if sum({table.hours},{table.date},"weekly") > 44 then
sum({table.hours},{table.date},"weekly") - 44

Then to get a biweekly and grand total summary, create the following formulas:

//{@reset} to be placed in the Grp#1 header:
whileprintingrecords;
numbervar biwkregtot := 0;
numbervar biwkottot := 0;

//{@accum} to be placed in the GH#2 header or footer:
whileprintingrecords;
numbervar biwkregtot := biwkregtot + {@regular};
numbervar biwkottot := biwkottot + {@overtime};
numbervar reggrtot := reggrtot + {@regular};
numbervar otgrtot := otgrtot + {@overtime};

//{@displbiwkreg} to be placed in GF#1:
whileprintingrecords;
numbervar biwkregtot;

//{@displbiwkottot} to be placed in GF#1:
whileprintingrecords;
numbervar biwkottot;

//{@displreggrtot} to be placed in the report footer:
whileprintingrecords;
numbervar reggrtot;

//{@displotgrtot} to be placed in the report footer:
whileprintingrecords;
numbervar otgrtot;

-LB

 

Thanks for the formulas but getting errors.

I REALLY REALLY do appreciate your help.

I did not see a crosstab in the Group #2 header (week).

I will breakdown the report design with formulas to give you a better understanding of it. Without seeing it I do not know how to better explain it.

Remember I'm new to this. I have edited and changed other Crystal Report but never ran into these types of problems before but for some reason this date and time stuff has me stumped.
____________________________________________________________
Report Header
____________________________________________________________
Page Header
____________________________________________________________
Page Header A
____________________________________________________________
Page Header B
____________________________________________________________
Group Header #1 Cashier.Name - A

//{@HeadingWeekend}
"Week ending " + ToText({@WeekEndDate})

//{@HeadingSun}
DateVar ThisDate := {@DateIn} + 1 - DayOfWeek ({@DateIn});

"Sun " + ToText(Month(ThisDate), 0) +"/" + ToText(Day(ThisDate), 0)

//{@HeadingMon}
DateVar ThisDate := {@DateIn} + 2 - DayOfWeek ({@DateIn});

"Mon " + ToText(Month(ThisDate), 0) +"/" + ToText(Day(ThisDate), 0)

//{@HeadingTues}
DateVar ThisDate := {@DateIn} + 3 - DayOfWeek ({@DateIn});

"Tues " + ToText(Month(ThisDate), 0) +"/" + ToText(Day(ThisDate), 0)

//{@HeadingWed}
DateVar ThisDate := {@DateIn} + 4 - DayOfWeek ({@DateIn});

"Wed " + ToText(Month(ThisDate), 0) +"/" + ToText(Day(ThisDate), 0)

//{2HeadingThu}
DateVar ThisDate := {@DateIn} + 5 - DayOfWeek ({@DateIn});

"Thu " + ToText(Month(ThisDate), 0) +"/" + ToText(Day(ThisDate), 0)

//{@HeadingFri}
DateVar ThisDate := {@DateIn} + 6 - DayOfWeek ({@DateIn});

"Fri " + ToText(Month(ThisDate), 0) +"/" + ToText(Day(ThisDate), 0)

//{@HeadingSat}
DateVar ThisDate := {@DateIn} + 7 - DayOfWeek ({@DateIn});

"Sat " + ToText(Month(ThisDate), 0) +"/" + ToText(Day(ThisDate), 0)
__________________________________________________________
Group Header #2 @WeekStartDate -() A

Sum of @HoursSun
Sum of @HoursMon
Sum of @HoursTue
Sum of @HoursWed
Sum of @HoursThu
Sum of @HoursFri
Sum of @HoursSat
Sum of @Hours
__________________________________________________________
Group Header #3 Cashier.Name - A

TEXT Date In
TEXT Date Out
TEXT Hours
___________________________________________________________
Details

//{@HoursSun}
IF DayOfWeek ({@DateIn}) = 1 THEN
{@Hours}
ELSE
0

//{@HoursMon}
IF DayOfWeek ({@DateIn}) = 2 THEN
{@Hours}
ELSE
0

//{@HoursTue}
IF DayOfWeek ({@DateIn}) = 3 THEN
{@Hours}
ELSE
0

//{@HoursWed}
IF DayOfWeek ({@DateIn}) = 4 THEN
{@Hours}
ELSE
0

//{@HoursThu}
IF DayOfWeek ({@DateIn}) = 5 THEN
{@Hours}
ELSE
0

//{@HoursFri}
IF DayOfWeek ({@DateIn}) = 6 THEN
{@Hours}
ELSE
0

//{@HoursSat}
IF DayOfWeek ({@DateIn}) = 7 THEN
{@Hours}
ELSE
0

//{@Hours}
IF IsNull({TimeClock.TimeIn}) OR IsNull({TimeClock.TimeOut}) THEN
0
ELSE
(DTSToSeconds ({TimeClock.TimeOut}) - DTSToSeconds ({TimeClock.TimeIn})) / 3600.0 + ({@DateOut} - {@DateIn}) * 24

//{@WeekStartDate}
{@DateIn} + 1 - DayOfWeek ({@DateIn})

//{@WeekEndDate}
{@DateIn} + 7 - DayOfWeek ({@DateIn})

//{@DateIn}
DTSToDate ({TimeClock.TimeIn})

//{@DateOut}
DTSToDate ({TimeClock.TimeOut})
___________________________________________________________
Group Footer #3 Cashier.Name - A
___________________________________________________________
Group Footer #2 @WeekStartDate -() A
___________________________________________________________
Group Footer #1 Cashier.Name - A

SUM of @Hours
___________________________________________________________
Page Footer
___________________________________________________________
Report Footer
___________________________________________________________
Report Footer A

SUM of @Hours
___________________________________________________________
Report Footer B


PS

I just thought of this. If there is a HOLIDAY during week 1 or week 2 or both, how could a total for HOLIDAY HOUR be added to pick the date and hours worked for that date or dates.

I guess I that could always add a {@HolidayDate}, in the DEF File filter.

; Description Field Name Type Level Crit. G.T. #
; --------------- --------------------- ---- ----- ------ ------
FIELD = Holiday Date, {@HolidayDate}, 2, 2, -1, 0

 

[lbass]

To get the date labels to repeat for each week, drag them from the GH#1 to GH#2 (week group).

If you are getting errors with the formulas, you need to say what the error messages are and where the cursor is when you get the message. Be sure to substitute your own field, table, and/or formula names into the suggested formulas.

-LB

 

[fabianor]

Thanks again lbass.

I did get the date labels to repeat for each week, moved them from the GH#1 to GH#2.

I entered the formulas and did not get any errors but it is not reporting correctly.

I do not see my error(s).

I was hoping that maybe I could email you a sample of what I could and you can see where I went wrong.

Once again I really do appreciate your help and I so close to being done.

 

[lbass]

You need to show an example of your results at the detail and group levels, so we can see what's going on. As I said earlier, we need to stay within the forum.

-LB

 

[fabianor]

These are the latest Results.

____________________________________________________________
Report Header
____________________________________________________________
Page Header
____________________________________________________________
Page Header A
Bi-Weekly Timecard Report As of: 07/28/2004
Sort Order: 1. Cashier Name
2. Week Starting Date
Selection Criteria: Week Starting Date Equal(=)July 4, 2004 OR
Week Starting Date Equal(=)July 11,2004

____________________________________________________________
Page Header B
____________________________________________________________
Group Header #1 Cashier.Name

//{@reset} returning result of 0.00

____________________________________________________________
Group Header #2 @WeekStartDate -() A

Week starting 07/04/2004
Sun 7/4 Mon 7/5 Tue7/6 Wed 7/7 Thur 7/8Fri 7/9Sat7/10Total
1 Administrator
2.00 4.00 2.00 2.00 2.00 2.00 2.00 16.00

//{@regular} returning result of 44.00
//{@Overtime} returning result of 64.93
//{@accum} returning result of 64.93

Week Ending 07/10/2004
Time In: Time Out: Hours:
07/10/2004 08:00:51 07/10/2004 10:00:51 2.00
07/09/2004 08:00:51 07/09/2004 10:00:51 2.00
07/08/2004 08:00:51 07/08/2004 10:00:51 2.00
07/07/2004 08:00:51 07/07/2004 10:00:51 2.00
07/06/2004 08:00:51 07/06/2004 10:00:51 2.00
07/05/2004 08:00:51 07/05/2004 12:00:51 4.00
07/04/2004 08:00:51 07/04/2004 10:00:51 2.00


Week Starting 07/11/2004
Sun7/11 Mon7/12 Tue7/13Wed 7/14Thur 7/15Fri7/16Sat7/17 Total
1 Administrator
8.00 8.00 8.00 8.00 8.00 8.00 8.00 56.00

Week Ending 07/17/2004
Time In: Time Out: Hours:
07/11/2004 08:000:00 07/11/2004 04:000:00 8.00
07/12/2004 08:000:00 07/12/2004 04:000:00 8.00
07/13/2004 08:000:00 07/13/2004 04:000:00 8.00
07/14/2004 08:000:00 07/14/2004 04:000:00 8.00
07/15/2004 08:000:00 07/15/2004 04:000:00 8.00
07/16/2004 08:000:00 07/16/2004 04:000:00 8.00
07/17/2004 08:000:00 07/17/2004 04:000:00 8.00

//{@regular} returning result of 44.00
//{@Overtime} returning result of 64.93
//{@accum} returning result of 129.86
____________________________________________________________
Group Footer #3 Cashier.Name - A
___________________________________________________________
Group Footer #2 @WeekStartDate -() A
___________________________________________________________
Group Footer #1 Cashier.Name - A

Total Hours for Bi-Weekly Pay Period 72.00

//{@displbiwkreg}returning result of 88.00
//{@displbiwkottot}returning result of 129.86

___________________________________________________________
Report Footer a
___________________________________________________________
Report Footer b
____________________________________________________________
Page Footer

Grand Total 72.00

//{@displreggrtot returning result of 88.00
//{@displotgrtot} returning result of 44.00

___________________________________________________________
Report Footer B

The Totals should be

*Total Hours Week 1 Reg Hrs 16.00 OT Hrs 0.00 Total 16.00

*Total Hours Week 2 Reg Hrs 44.00 OT Hrs 12.00 Total 56.00

*Bi-Weekly Hours Total Reg Hrs60.00 OT Hrs 12.00 Total 72.00

Grand Total Reg Hours for Pay Period 60.00 for all employees
Grand Total OT Hours for Pay Period 12.00 for all employees
Grand Total Hours for Pay Period 72.00 for all employees


****Is there a way to add a HOLIDAY Hours during week 1 or week 2 or both, how could a total for HOLIDAY HOUR be added to pick the date and hours worked for that date or dates.

Like the Total Hours for 7/4/04 is 2.00 and the Total hours for 7/16/04 is 8.00 then the Total Holiday Hours would be 10.00. I guess I that could always add a {@HolidayDate}

How does Crystal round of hours to the nearest 1/4 hours.

 

[lbass]

Please check where you are placing my formulas to make sure you have them in the right sections. There is no way you could get the results you are getting if these are placed in the right sections.

-LB

 

Could this be the problem?

I only have 4 Tables
Cashier.Number
Cashier.Name
TimeClock.TimeIn
TimeClock.TimeOut

These are the Sum formulas
Sum of @HoursSun
Sum of @HoursMon
Sum of @HoursTue
Sum of @HoursWed
Sum of @HoursThu
Sum of @HoursFri
Sum of @HoursSat
Sum of @Hours

All the other formulas make up the date and Time In and Out and are work correctly.
_________________________________________________________
This is where I placed your formulas I believe that they are in the right sections.

//{@reset} returning result of 0.00 and is inserted in Group Header #1 Cashier.Name
________________________________________________________

//{@regular} returning result of 44.00
//{@Overtime} returning result of 64.93
//{@accum} returning result of 64.93

are all inserted in Group Header #2 @WeekStartDate -() A
_________________________________________________________

//{@displbiwkreg}returning result of 88.00
//{@displbiwkottot}returning result of 129.86

are both inserted in Group Footer #1 Cashier.Name - A
__________________________________________________________

I have nothing else in Group Header #1 Cashier.Name

just //{@reset}

In Group Header #2 @WeekStartDate -() A

WeekStarting 7/7/04
Sum of @HoursSun returning 2.00
Sum of @HoursMon returning 4.00
Sum of @HoursTue returning 2.00
Sum of @HoursWed returning 2.00
Sum of @HoursThu returning 2.00
Sum of @HoursFri returning 2.00
Sum of @HoursSat returning 2.00
Sum of @Hours returning Total of 16.00

WeekStarting 7/11/04
Sum of @HoursSun returning 8.00
Sum of @HoursMon returning 8.00
Sum of @HoursTue returning 8.00
Sum of @HoursWed returning 8.00
Sum of @HoursThu returning 8.00
Sum of @HoursFri returning 8.00
Sum of @HoursSat returning 8.00
Sum of @Hours returning Total of 56.00
__________________________________________________________
Group Footer #1 Cashier.Name - A

Sum of @Hours
Total Hours for Bi-Weekly Pay Period 72.00
___________________________________________________________
Report Footer A

Grand Total 72.00

 

[fabianor]

*****THIS COULD BE THE PROBLEM*****

I thought I had mentioned this earlier but I did not type into message.

For your suggestion of

//{@regular} to be placed in the GH#2 header or footer:
if sum({table.hours},{table.date},"weekly") <= 44 then
{table.hours} else 44

I used
//If Sum ({@Hours}) <=44 then ({@Hours}) else 44

and for the following

//{@Overtime} to be placed in the GH#2 header or footer:
if sum({table.hours},{table.date},"weekly") > 44 then
sum({table.hours},{table.date},"weekly") - 44

I used

If Sum ({@Hours}) >44 then Sum ({@Hours}) - 44

Was not sure what to change this to as per above posting. I only have 4 Table fields.

Cashier.Number
Cashier.Name
TimeClock.TimeIn
TimeClock.TimeOut

and the Hours are all computed with sum formula.

Sum of @HoursSun
Sum of @HoursMon
Sum of @HoursTue
Sum of @HoursWed
Sum of @HoursThu
Sum of @HoursFri
Sum of @HoursSat
Sum of @Hours

 

[lbass]

Yes, that is the problem. You have to use my original formulas and use whatever you are grouping on for group#2 which you have labeled as {@weekstartdate}. If {@weekstartdate} is the group field, use that (I wasn't sure whether this was just a label formula). Go to report->change group expert and observe what is your group #2 and use that in the formulas.

-LB

 

[fabianor]

***I got it to work.*** I just have a couple other questions and should be set to go. My report looks great now thanks to you.

This was the magic formula.

//{@regular}
If Sum ({@Hours}, {@WeekStartDate}, "daily")<=44 then
Sum ({@Hours}, {@WeekStartDate}, "daily") else 44

I had to get rid of "weekly" and change to "daily and add Sum.
Once I got {@regular} work {overtime} was easy to fix.

//{overtime}
If Sum ({@Hours}, {@WeekStartDate}, "daily")>44 then
Sum ({@Hours}, {@WeekStartDate}, "daily") -44

****The only other thing I want to add is an {@Holiday}****

For example the 4th of July was a Holiday so I just want the Total Hours for that day to show up as Holiday Hours for time and a half pay in it own field.

If there are two holidays in the same week or following week to add together in weekly and Biweekly Totals.

Also, how do you get the Time in Crystal to Round to the nearest 1/4 hour. Would like to see 6.50 hours and not 6.36

I have also sent all the formulas used in this Report for anyone who may be interest.
____________________________________________________________
These are the Report Fields

{Cashier.Number}
{TimeClock.TimeIn}
{TimeClock.TimeOut}
{Cashier.Name}

These are the x1

//{@Accum}
whileprintingrecords;
numbervar biwkregtot := biwkregtot + {@regular};
numbervar biwkottot := biwkottot + {@overtime};
numbervar reggrtot := reggrtot + {@regular};
numbervar otgrtot := otgrtot + {@overtime};

//{@DateIn}
DTSToDate ({TimeClock.TimeIn})

//{@DateOut}
DTSToDate ({TimeClock.TimeOut})

//{@displbiwkottot}
whileprintingrecords;
numbervar biwkottot;

//{@displbiwkreg}
whileprintingrecords;
numbervar biwkregtot;

//{@displotgrtot}
whileprintingrecords;
numbervar otgrtot

//displreggrtot}
whileprintingrecords;
numbervar reggrtot;

//HeadingWeek}
"Week ending " + ToText({@WeekEndDate})

//{@HeadingSun}
DateVar ThisDate := {@DateIn} + 1 - DayOfWeek ({@DateIn});

"Sun " + ToText(Month(ThisDate), 0) +"/" + ToText(Day(ThisDate), 0)

//{@HeadingMon}
DateVar ThisDate := {@DateIn} + 2 - DayOfWeek ({@DateIn});

"Mon " + ToText(Month(ThisDate), 0) +"/" + ToText(Day(ThisDate), 0)

//{@HeadingTue}
DateVar ThisDate := {@DateIn} + 3 - DayOfWeek ({@DateIn});

"Tue " + ToText(Month(ThisDate), 0) +"/" + ToText(Day(ThisDate), 0)

//{@HeadingWed}
DateVar ThisDate := {@DateIn} + 4 - DayOfWeek ({@DateIn});

"Wed " + ToText(Month(ThisDate), 0) +"/" + ToText(Day(ThisDate), 0)

//{@HeadingThu}
DateVar ThisDate := {@DateIn} + 5 - DayOfWeek ({@DateIn});

"Thu " + ToText(Month(ThisDate), 0) +"/" + ToText(Day(ThisDate), 0)

//{@HeadingFri}
DateVar ThisDate := {@DateIn} + 6 - DayOfWeek ({@DateIn});

"Fri " + ToText(Month(ThisDate), 0) +"/" + ToText(Day(ThisDate), 0)

//{@HeadingSat}
DateVar ThisDate := {@DateIn} + 7 - DayOfWeek ({@DateIn});

"Sat " + ToText(Month(ThisDate), 0) +"/" + ToText(Day(ThisDate), 0)

//{@HoursSun}
IF DayOfWeek ({@DateIn}) = 1 THEN
{@Hours}
ELSE
0

//{@HoursMon}
IF DayOfWeek ({@DateIn}) = 2 THEN
{@Hours}
ELSE
0

//{@HoursTue}
IF DayOfWeek ({@DateIn}) = 3 THEN
{@Hours}
ELSE
0

//{@HoursWed}
IF DayOfWeek ({@DateIn}) = 4 THEN
{@Hours}
ELSE
0


//{@HoursThu}
IF DayOfWeek ({@DateIn}) = 5 THEN
{@Hours}
ELSE
0

//{@HoursFri}
IF DayOfWeek ({@DateIn}) = 6 THEN
{@Hours}
ELSE
0

//{@HoursSat}
IF DayOfWeek ({@DateIn}) = 1 THEN
{@Hours}
ELSE
0

//{@Hours}
IF IsNull({TimeClock.TimeIn}) OR IsNull({TimeClock.TimeOut}) THEN
0
ELSE
(DTSToSeconds ({TimeClock.TimeOut}) - DTSToSeconds ({TimeClock.TimeIn})) / 3600.0 + ({@DateOut} - {@DateIn}) * 24

//{@overtime}
If Sum ({@Hours}, {@WeekStartDate}, "daily")>44 then
Sum ({@Hours}, {@WeekStartDate}, "daily") -44

//{@regular)
If Sum ({@Hours}, {@WeekStartDate}, "daily")<=44 then
Sum ({@Hours}, {@WeekStartDate}, "daily") else 44

//{@Reset}
whileprintingrecords;
numbervar biwkregtot := 0;
numbervar biwkottot := 0;

//{@SelectionCriteria}
"Week Starting Date Equal (=) July 4, 2004 OR

//{@SortOrder}
"1. Cashier Name 2. Week Starting Date"

//{@TimeIn}
DTSToTimeString ({TimeClock.TimeIn})[1 to Length(DTSToTimeString ({TimeClock.TimeIn})) - 3]

//{@TimeOut}
DTSToTimeString ({TimeClock.TimeOut})[1 to Length(DTSToTimeString ({TimeClock.Timeout})) - 3]

//{@WeekEndDate}
{@DateIn} + 7 - DayOfWeek ({@DateIn})

//{@WeekStartDate}
{@DateIn} + 1 - DayOfWeek ({@DateIn})
____________________________________________________________

Group #1
GroupName ({Cashier.Name}
____________________________________________________________

Group #2
GroupName ({@WeekStartDate}, "daily")
____________________________________________________________

Group #3
GroupName ({Cashier.Name})
____________________________________________________________
Report Area: Sum of Hours
Sum ({@Hours})
____________________________________________________________

Group #1: Cashier.Name () – A:Sum of Hours

Sum ({@Hours}, {Cashier.Name})
____________________________________________________________
Group #2: WeekStartDate () –A: Sum of HoursSun
Sum ({@HoursSun}, {@WeekStartDate}, "daily")

Group #2: WeekStartDate () –A: Sum of HoursMon
Sum ({@HoursMon}, {@WeekStartDate}, "daily")

Group #2: WeekStartDate () –A: Sum of HoursTue
Sum ({@HoursTue}, {@WeekStartDate}, "daily")

Group #2: WeekStartDate () –A: Sum of HoursWed
Sum ({@HoursWed}, {@WeekStartDate}, "daily")

Group #2: WeekStartDate () –A: Sum of HoursThu
Sum ({@HoursThu}, {@WeekStartDate}, "daily")

Group #2: WeekStartDate () –A: Sum of HoursFri
Sum ({@HoursFri}, {@WeekStartDate}, "daily")

Group #2: WeekStartDate () –A: Sum of HoursSat
Sum ({@HoursSat}, {@WeekStartDate}, "daily")

Group #2: WeekStartDate () –A: Sum of Hours
Sum ({@Hours}, {@WeekStartDate}, "daily")

 

[lbass]

I don't know how you treat holidays on your database. If holiday hours do not have to be excluded from your other calculations, but only added separately, then create a formula like:

if {table.date} in [date(year({table.date}),07,04),date(year({table.date}),12,25),date(year({table.date}),01,01)] then 8 * 1.5

Include whatever holidays you observe within the brackets. This would need to be updated annually for holidays that are observed on different dates each year. Substitute your normal daily hours for "8". You can then right click on the formula and insert summaries at your group levels.

There might be a better way to do the rounding, but I think the following should work:

if val(right(totext(round({@hours},2),2),2)) in 0 to 24 then truncate({@hours},0) else
if val(right(totext(round({@hours},2),2),2)) in 25 to 74 then truncate({@hours}) + .5 else
if val(right(totext(round({@hours},2),2),2)) in 75 to 99 then truncate({@hours}) + 1

-LB

 

[fabianor]

I should have explained myself a bit better for these two last items.

The formula for holidays does work but I just need it to pull the total hours worked for the date of the holiday
And summarized like (Sum of HolidayHours)

Example 4th of July Holiday and 15th of July Holiday

Sun 7/4 Mon 7/5 Tue7/6 Wed 7/7 Thur7/8 Fri7/9 Sat7/10 Total
2.00 4.00 2.00 2.00 2.00 2.00 2.00 16.00

Regular Hours 16.00
Overtime Hours 0.00
Holiday Hours 2.00

Sun7/11 Mon7/12 Tue7/13 Wed7/14Thur7/15Fri7/16 Sat7/17 Total
8.00 8.00 8.00 8.00 8.00 8.00 8.00 56.00

Regular Hours 44.00
Overtime Hours 12.00
Holiday Hours 8.00

Total Hours 72.00
Bi Weekly Regular Hours 60.00
Bi WeeklyOvertime Hours 12.00
Bi Weekly Holiday Hours 10.00

For Rounding I just need the Totals for

SUM OF REGULAR
SUM OF OVERTIME
SUM OF Holiday

To round to the nearest quarter hour.

 

[lbass]

Don't know what your question is. If you want the holiday hours to reflect actual hours worked, then change the formula to:

if {table.date} in [date(year({table.date}),07,04),date(year({table.date}),12,25),date(year({table.date}),01,01)] then {table.hours}

Then insert summaries on it.

For rounding, just plug each summary formula into the rounding formula instead of {@hours}.

-LB

 

[fabianor]

I LB

What do you mean by for rounding, just plug each summary formula into the rounding formula instead of {@hours}.

Do you mean this or to replace {@hours} with {@regular) because it tried both ways a got an error.

//{@regular)
If Sum ({@Hours}, {@WeekStartDate}, "daily")<=44 then
Sum ({@Hours}, {@WeekStartDate}, "daily") else 44
if val(right(totext(round({@hours},2),2),2)) in 0 to 24 then truncate({@hours},0) else
if val(right(totext(round({@hours},2),2),2)) in 25 to 74 then truncate({@hours}) + .5 else
if val(right(totext(round({@hours},2),2),2)) in 75 to 99 then truncate({@hours}) + 1

Because I just want the Totals to be round in the summaries for
Regular Hours
Overtime Hours
Total Hours
Bi Weekly Regular Hours
Bi WeeklyOvertime Hours

Also I tried this for Holiday

if {@WeekStartDate} in [date(year({@WeekStartDate}),07,04),date(year
({@WeekStartDate}),07,05),date(year
({@WeekStartDate}),07,11)date(year
({@WeekStartDate}),07,12),date(year({@WeekStartDate}),01,01)] then {@Hours}

Does not work. It show hours enter hours for 7/04 and 7/11 since start of week but not for 7/05 or 7/12 and does not add hours for dates together.

Should I use {@DateIn})
IF DayOfWeek ({@DateIn}),07,04)] THEN {@Hours}

Not following anymore.

Thanks