Solved

Crystal Report Calculating Shift Hours

Posted on 2014-12-30
18
340 Views
Last Modified: 2015-02-03
Working on a report where I need to calculate Shift Hours which alternate.  

For example: 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.  Any help with this greatly appreciated.  There is a field for each day In the database.
0
Comment
Question by:nobile00
  • 9
  • 5
  • 4
18 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 40524798
WHat fields are in the database?

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

mlmcc
0
 

Author Comment

by:nobile00
ID: 40524811
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.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40524955
What other columns are there?

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

mlmcc
0
 

Author Comment

by:nobile00
ID: 40529375
I am using Crystal to look at the data.  I can take a look on Monday to see if there is any other columns
0
 

Author Comment

by:nobile00
ID: 40536762
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
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40537142
What information is in the database regarding when they worked?

Can you provide some sample data to work with?

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 40537584
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
0
 

Author Comment

by:nobile00
ID: 40539029
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
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40539371
What do you enter for start and end date?

Which formula is giving you trouble?

mlmcc
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:nobile00
ID: 40539385
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!
0
 
LVL 100

Expert Comment

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

mlmcc
0
 

Author Comment

by:nobile00
ID: 40539457
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
0
 
LVL 34

Accepted Solution

by:
James0628 earned 500 total points
ID: 40541877
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?

 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.


 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?


 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


 It's not a big deal, but I thought I'd mention it.  It might be useful in the new formula.

 James
0
 

Author Comment

by:nobile00
ID: 40545319
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
0
 
LVL 34

Expert Comment

by:James0628
ID: 40546342
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
0
 

Author Comment

by:nobile00
ID: 40547641
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
0
 
LVL 34

Expert Comment

by:James0628
ID: 40547793
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
0
 

Author Closing Comment

by:nobile00
ID: 40586674
Thanks the formula worked!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now