Variable work days & hours combined into a Timesheet by Day & Date

[RedSparks]

Crystal 8.5

I am creating a timesheet form based on the scheduled hours our foster grandparents work. They can have flexible schedules. It's the flexible schedules I'm having a problem with. My timesheet section is only picking up the last detail line. I need it to pick up and evaluate every line of detail and place it next to appropriate date and day in the timesheet section of the report.

Example of detail lines return for 1 person.
Scheduled from 9 am to 11 am 2hrs {@ScheduledHrs}
on Mon, Thu
1:30 pm to 4 pm 2.50hrs Mon, Wed
9 am to 12:30pm 3.50 hrs Fri

{@ScheduledHrs} = calculates the total scheduled hours.

Timesheet Setup:

Work Day Date Hrs Scheduled
Monday 08/15/05 4.50 (need to add the afternoon and morning hours together)
Tuesday 08/16/05 (There are no hrs worked this day so this would be blank)
Wednesday 08/17/05 2.50
Thursday 08/18/05 2.00
Friday 08/19/05 3.50

Work hours formula: {@WkHrs3}
if {FD__FGP_ClientHours.Monday}="T" and {@DayofWk3}="Monday" then {@ScheduledHrs}
else if {FD__FGP_ClientHours.Tuesday}="T" and {@DayofWk3}="Tuesday" then {@ScheduledHrs}
else if {FD__FGP_ClientHours.Wednesday}="T" and {@DayofWk3}="Wednesday" then {@ScheduledHrs}
else if {FD__FGP_ClientHours.Thursday}="T" and {@DayofWk3}="Thursday" then {@ScheduledHrs}
else if {FD__FGP_ClientHours.Friday}="T" and {@DayofWk3}="Friday" then {@ScheduledHrs}

A formula was created for each day in the payroll period.

Assistance would be greatly appreciated!! Thanks

 

[Madawc]

I don't follow what you're doing. When I've done timesheets through Crystal, I have grouped the records by day and by week. This automatically sorts the data into a sensible sequence and also makes totaling simlpe.

If you have several records with entries for one or more days of the week, that is trickier. I'd do it by running totals that each accumulated for one particular day of the week.

If you need more help, please explain the structure of your data.

Madawc Williams (East Anglia, UK)

 

[RedSparks]

For each day of the week the individual time is provided by denoting the time they will work from and to and then the day of the week is check off that this time will apply too.

Example:
Time from: 9am Time To: 11am
Days checked that this applies to: x Mon Tue Wed x Thu Fri
Time from 1:30pm Time To: 4pm
Days Checked that this applies to: x Mon Tue x Wed Thu Fri
Time from 9am Time To: 12:30pm
Days Checked that this applies to: Mon Tue Wed Thu x Fri

The days checked come through as "T" for true, otherwise they are "F" for false.

In a Timesheet, I need to total by day / date the total scheduled hours, instead of actual hours worked.

Do you need any other information?

 

[Madawc]

You'll need a formula field that finds the difference between the two times for each record. (Probably DateDiff with the 'time' option, but the database I work on does not have times.) Then you need to accumulate the results for the relevant days, using separate running totals that add if their own day is checked. (It should be OK to do a running total for a value calculated for a single 'row'.)

Madawc Williams (East Anglia, UK)