Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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.

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.

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

mlmcc

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

Can you provide some sample data to work with?

mlmcc

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 SatDo 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

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

mlmcc

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

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?

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.

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

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