Crystal Report Calculating Shift Hours

WHat fields are in the database?

Do you have  field that has the hours worked each day?

mlmcc

As far as I can see it is each day with hours not a friendly database. So fields are mon through sun with each having 10.5 hours each.

What other columns are there?

Can you look at the data in the database or are you using Crystal to access the data?

mlmcc

I am using Crystal to look at the data.  I can take a look on Monday to see if there is any other columns

There is no single field that has how many hours the person needs to work but they need to work 10.5 hours per day

For a regular 9-5 shift  8 hours a day they are using the gollowing:
IF {wh_resource.hire_date} > {?Start Date} THEN
(DateDiff ("d", {wh_resource.hire_date}, {?End Date}) -
DateDiff ("ww", {wh_resource.hire_date}, {?End Date}, crSaturday) -
DateDiff ("ww", {wh_resource.hire_date}, {?End Date}, crSunday))*8
ELSE
(DateDiff ("d", {?Start Date}, {?End Date}) -
DateDiff ("ww", {?Start Date}, {?End Date}, crSaturday) -
DateDiff ("ww", {?Start Date}, {?End Date}, crSunday))*8

I don't see if that can work for the scenario:
One shift is goes 2 weeks first week Sun. Wed and Thurs Second week Mon Tues Fri and Sat then runs again third week Sun. Wed and Thurs and fourth week  Mon Tues Fri and Sat the hours are 7am-7pm hours to work are 10.5 hours a day.  Then there is another shift that is opposite from the above.  Then there are two other shifts that do the same as above but work from 7pm to 7am same 10.5 hours.

Suggestions

What information is in the database regarding when they worked?

Can you provide some sample data to work with?

mlmcc

What are you actually trying to calculate?  The hours that someone actually worked (in which case you'd presumably need to know which days they actually worked, and have "clock in" and "clock out" times or something similar) ?  Or just the total hours possible for a shift (assuming that someone worked a full day for every day in that shift) ?  The "9-5" formula that you posted seems to be calculating the latter.

One shift is goes 2 weeks first week Sun. Wed and Thurs Second week Mon Tues Fri and Sat then runs again third week Sun. Wed and Thurs and fourth week  Mon Tues Fri and Sat

Do the 3rd and 4th weeks matter?  If they're just the same as the 1st and 2nd weeks, why not just look at it as a 2 week pattern that repeats?  Su-W-Th M-Tu-F-Sa --- Su-W-Th M-Tu-F-Sa --- Su-W-Th M-Tu-F-Sa --- and so on.  Is the 4 week period significant in some way?

 Given the different number of works days in each week (3 or 4), you'll need some way to know which week is which.  For example, for that first shift, is this week a Su-W-Th week, or a M-Tu-F-Sa week?  And you presumably have some kind of shift indicator for each person.

 James

What I am trying to calculate is their Available Hours for each month based on their shifts at 10.5 hours a day.

For example the Month of November:
11/1 would be  Mon Tues Fri Sat
11/2-11/8 would be Sun Wed Thurs
11/9-11/15 would be  Mon Tues Fri Sat
11/16-11/20 would be Sun Wed Thurs
11/23-11/29 would be  Mon Tues Fri Sat
11/30 would start Sun Wed Thurs
then just keeps repeating into each month.

There is a field that contains hours worked but that is an easy calculation.
I attached a copy of the report but unfortunately I am unable to connect to the database today to save data to the report I am doing this report through a WebEx and the person is not available the rest of the week to do the WebEx.  If the report doesn't help I will be online with the WebEx on Monday.
NOC-UtilizationNew1.rpt

What do you enter for start and end date?

Which formula is giving you trouble?

mlmcc

The formula that is an issue is Available Hours based on the shifts and varying days for each month.  Not sure what you mean by start and end date?  Thanks for all the help much appreciated!

WHen you run the report it prompts for a start and end date.  The values are used in that formula.

mlmcc

The start and end date are parameters used for the range the report would run.  Example: 11/1/14 to 12/31/14 hope that makes sense

Answers:
Maybe I'm missing something, but in your November example, you said that 11/1 (a Saturday) was part of a M-Tu-F-Sa week.  How do you know that?  If a person is alternating between M-Tu-F-Sa and Su-W-Th, couldn't 11/1 be the end of a Su-W-Th week for them, meaning that they didn't work on 11/1?

According to worked hours he did have hours worked for 11/1

If you know which shift week (first or second) was active on the starting date, then I guess it comes down to figuring out how many days between the starting and ending dates were in each shift.  Using your example, there'd be 1 day for 11/1, then 3 days (Su-W-Th) for the next week, then 4 days for the week after that, and so on.

This is correct

Also, FWIW, the "9-5" formula that you posted could be simplified a bit.  Instead of using an If-Then to compare the hire date and start date and use whichever is later, you could just use the Maximum function to pick the latest date.

 (DateDiff ("d", Maximum ([ {wh_resource.hire_date}, {?Start Date} ]), {?End Date}) -
DateDiff ("ww", Maximum ([ {wh_resource.hire_date}, {?Start Date} ]), {?End Date}, crSaturday) -
DateDiff ("ww", Maximum ([ {wh_resource.hire_date}, {?Start Date} ]), {?End Date}, crSunday))*8

For the above the formula would include Sat and Sun if they worked those days in their  shift?

 You also mentioned a night shift from 7 PM to 7 AM.  That will cross date boundaries (a shift could start on the day before your start date, or end on the day after your end date).  If that happens, do you want to count the whole shift, or stop at midnight?
 
Would need to count the whole shift 7-7

According to worked hours he did have hours worked for 11/1

 So the only way to know which shift someone is working in a given week is to look at their hours?  What if he was supposed to work on 11/1, but was sick, so he didn't have any hours that day?

 In broader terms, is there some simple indicator for which days a person is supposed to work each week, or do you have to actually look at which days they had hours for in a given week, and try to figure it out from that?

 If it's the latter, what if they were out sick all week or something?  As I understand it, you'd still want to know the hours that they _could_ have worked that week, but they wouldn't have any hours to tell you which days they were supposed to work.  Or, for that matter, could someone work an extra day one week (eg. filling in for someone else)?  Then if you looked at the days that they had hours for, you'd get conflicting results indicating that they were assigned to both shifts.

For the above the formula would include Sat and Sun if they worked those days in their  shift?

 Not as I understand things.  I was just showing a different way of writing the "9-5" formula that you posted, which seems to be for a simple M - F shift.  I was just showing how you could eliminate the If-Then in that formula by using Maximum.  The formula would still produce the same result.  That was more of an "FYI" thing, although using Maximum like that might be useful in the new formula, depending on how complicated it gets.

 James

See answers to questions below in bold Thanks for your help!

So the only way to know which shift someone is working in a given week is to look at their hours?  What if he was supposed to work on 11/1, but was sick, so he didn't have any hours that day?

Sick Time is calculated in PTO then subtracted from available hours for the Adjusted Hours

In broader terms, is there some simple indicator for which days a person is supposed to work each week, or do you have to actually look at which days they had hours for in a given week, and try to figure it out from that?
Just have based on shift days no indicator

 If it's the latter, what if they were out sick all week or something?  As I understand it, you'd still want to know the hours that they _could_ have worked that week, but they wouldn't have any hours to tell you which days they were supposed to work.  Or, for that matter, could someone work an extra day one week (eg. filling in for someone else)?  Then if you looked at the days that they had hours for, you'd get conflicting results indicating that they were assigned to both shifts.

All is based on the shift days and  based on the information I have they only work their shift days/weeks and Sick and vacation is calculated in PTO and subtracted from Available hours

If you're going to have to read at least some of the records with the employee hours anyway, to know which shift they worked, and if they will always have a record for every day in their shift (even if they were sick, etc.), then the simplest thing might be to just read through all of the time records for the requested date range, and group by the employee and then the day (or vice versa), and then have a formula in the inner group header or footer that adds 10.5 to a variable for each group (either each day for each employee, or each employee for each day).  Assuming that every employee has one record for every day in their shift, during the time period that you select (from {?Start Date} to {?End Date}), then it seems like that could give you the total that you're looking for.

 I don't think you've really said much about how you actually want to use/present these hours, so I have no idea how well that idea might actually work for you.

 James

Thanks the formula worked!