PDA

View Full Version : Calculate sub shift hours



ioncila
04-02-2014, 03:55 AM
Hi
I'm doing a crosspost
http://www.excelforum.com/excel-general/1000945-calculate-several-sub-shifts-hours.html
because i really need help to solve this issue and it seems that the first attempt to help did not succeed.
I am sorry for that.

My issue is:
I know how to calculate day shift and night shift hours.
Now I need to calculate some periods into the day and night shift:

Night Shift is 22:00 to 6:00 and day shift is 6:00 to 22:00.
However, some work has to be done in some periods of the shift and every day is different

For example:
1st Sub shift - 00:50 to 04:20
2nd Sub shift - 05:20 to 06:50
3rd Sub shift - 12:50 to 13:50
4th Sub shift - 16:00 to 16:30

I have tried many ways and spent mauch time googling and consulting many foruns, including vbaexpress. No success and no similar issues found.
So how to get a formula to calculate total of day subshift and night subshift hours each day?

Once again, sorry for the crosspost
Thanks in advance

GTO
04-02-2014, 04:16 AM
I did not look at your workbook. There may well be a worksheet function only solution. Would you be interested in applying a VBA solution?

Mark

ioncila
04-02-2014, 04:33 AM
Thank you very much for your quick reply
Although it is a faster and more reliable solution, I'm trying to avoid a VBA solution.
If there is no alternative in formulas, I will try VBA.

SamT
04-02-2014, 07:25 AM
I mostly do VBA and not formulas, but I think that IF, AND, and OR might work

Using the formula in the Hours Column (P):
=MOD((H6-G6)+(J6-I6)+(L6-K6)+(N6-M6),1)looking at each shift pair

"(H6-G6)" and "(J6+I6)" and etc
For the day shift total

MOD((IF(AND(H6>5:59,H6<22:00),H6-G6,0))+Next shift pair+etc)
For the night shift

MOD((IF(OR(H6>21:59,H6<6:00),H6-G6,0))+Next shift pair+etc)

This assumes that the subshift belongs to the main shift in which it started. For example The 4th subshift of 3/12/2014, (21:50 to 22:20,) on your sheet belongs to the day shift. THis also means that a subshift from 21:59 to 6:01 the next day belongs to the day shift of the starting date.

ioncila
04-02-2014, 09:33 AM
Hi
Thank you for your reply.
However, that is one of the solutions I have hardly tried to develop. It doesn't work as I want in this point:
For the example you picked (3/12/2014 - 21:50 to 22:20), calculation should result in 00:10 minutes for dayshift (21:50 to 22:00) and 00:20 minutes for nightshift (22:00 to 22:20). And I can't find the way.
Unfortunelly, I'm realizing that this cannot be done by formulas

GTO
04-02-2014, 01:10 PM
Reference your example calculations for March 3 and 4: I get (in my not-too-awake noggin) the same results for the 4th, but different for the 3rd.




F

G

H

I

J

K

L

M

N









Day

Night

Day

Night

Day

Night

Day

Night


Day

Night




8

3/3/2014


210

50

40

60


30



140

250




9

3/4/2014


120


80

80





80

200






For the 3rd, I get 140 minutes (or 2:20) for the day shift, and 250 minutes (or 4:10) for the night shift. Might have you erred in calculating, or am I looking at this wrong?

Mark

Aussiebear
04-02-2014, 04:09 PM
The confusion comes about because you are trying to match shift allocation with actual work times. My suggestion to to add additional columns (Which you may hide if you wish), and calculate each shift period with day or night work hours and then show a total day and night hours worked.

SamT
04-02-2014, 07:21 PM
The decimal equivalents of 6:00 and 22:00 are 0.25 and 0.916667. I suggest that you create two named formulas: BeginDays, RefersTo "=.25" nad EndDays, RefersTo "=.916667" and two for the night shift with a variance in values so they don't overlap.

And use those in your formulas in the helper columns AussieBear mentioned.

The resulting formlas will be a bit complicated, but once you get one Day part and Night part columns' formulas, you can paste them in the other helper columns

Total Day helper column formula structure
=IF(AND(Start> BeginDays, Start<EndDays,End>BeginDays, End<EndDays) End-Start, Else formula for start in day and end in night, else start in night and end in Day, else 0)))))))))))))))))...

PS: the way to discover those decimal equivalents is place the Time in A1, set B1 "=A1" and format B1 as a number with 6 or 7 decimal places. You probably want to use two rows so you can tell how many significant digits are needed to differentiate between 6:00:00 and 5:59:59

ioncila
04-03-2014, 04:49 AM
Following your reasoning (thank you so much for your support):
I built this formula for each subshift to calculate dayshift time
Subshift 1 - =IF(OR(G8=0,G8>0,916667,AND(G8<0,25,H8<0,25)),0,IF(AND(G8>=0,25,H8>G8,H8<0,916667),H8-G8,IF(AND(G8>=0,25,OR(H8<G8,H8>0,916667)),0,916667-G8,IF(AND(G8<0,25,H8>=0,25),H8-0,25))))
Then, sum all subshifts (cols T,U,V,W), subtract total hours (col P) and get nightshift time

Now, I'm going to use helper columns as suggested.
But, is there a way to compress these formulas? Say, how could I convert it in a named function (such as "=SShift1") and apply it to each cell of SShift1 column?

SamT
04-03-2014, 05:52 AM
@ All, the words "0,91667" and "0,25" in the above formula are numbers with commas as decimal points.

@ ioncila,

I don't have a clue.

Maybe? = If you used named ranges and named constants and entered the named formula into the cell as a CSE formula.

Leave this post up for a while, we have some real formula experts here at VBAX.

One advantage of cell formulas is that you can insert and delete blank rows and columns in the worksheet without effecting the formula results.

ioncila
04-15-2014, 03:38 PM
Hi
I am satisfied with the solution I have built (see post #9), done of course with your great support.
But, once my issue has evolved to another direction, I don't know if I close this thread and open a new one or if I stay here.

The issue remains on calculating subshift hours and I describe it in the attached file.
Since last weekend I was trying to adapt that formula to this new problem but it seems very difficult to me to see clearly.
So your help would be very very appreciated (once more)

Many thanks in advance
Ioncila

Aussiebear
04-15-2014, 04:33 PM
After having a look at your solution, I would hate to be an employer under these conditions. Wage costs are uncontrolled and almost impossible to budget for. But aside from that.....

I having trouble working out how a worker earns overtime? You have divided the day into 4 sections of 6 hour periods ( 0 - 6, 6- 12, 12-18, 18- 24) and you have pay rates allocated per quartile. Since worker 1 has worked 9:40 in total which comprises 1:15 hrs @ level 1, 6:0 hrs @ level 2, 2:25 hrs @ level 3 rates where does the overtime come in?

GTO
04-15-2014, 05:25 PM
Hi Ted,


...I would hate to be an employer under these conditions. Wage costs are uncontrolled and almost impossible to budget for. But aside from that.....

Yeh, but it'd be great to be an employee! LOL

@Ioncila:

Reference workers 8 and 9, I believe I see an issue, as both of these would return negative hours worked. Not thought through maybe, but by my recollection, we really need to have the date included, both for the in and out times. Either a column for the in date and a column for the out date, or include the date in the in and out columns. Basically because of this: Say I work from 1800 hours to 0200 hours. Without the date included, the return will be a negative value. Or let us say that I work from 1800 hours to 0700 hours (13 hours worked). Without the date(s) being considered, 1 hour will be returned for hours worked. Does that make sense?

Mark

Aussiebear
04-16-2014, 12:59 AM
Yeh, but it'd be great to be an employee! LOL

Be hard to see a business lasting very long under these conditions. Worker 1 for example is on overtime immediately when starting the day (according to the worksheet), then drops back to standard rates.... for 5 hours, then goes back to overtime. Its not logical from a business perspective.

GTO
04-16-2014, 01:50 AM
Agreed; it just seems so foreign to me I couldn't help but think, "Gosh, that'd be GREAT if I got paid extra for..." (meetings/training/mandatory stuff that is opposite of a schedule already somewhat opposite of what our Maker appears to have designed us for). It gave me a chuckle :-)

ioncila
04-16-2014, 02:30 AM
Be hard to see a business lasting very long under these conditions. Worker 1 for example is on overtime immediately when starting the day (according to the worksheet), then drops back to standard rates.... for 5 hours, then goes back to overtime. Its not logical from a business perspective.

First of all, many thanks for your replies.

Now, some clarification:
Per'haps I have used the wrong terms in my description.
This company has a payment system with 4 levels, as described in the sheet. This payment and shift system is related to the specific type of industrial production, it's not quite a overtime payment. In abstact, it's not very different from a night shift and day shift payment.

For example, Level 1 is paid $35/hour, Level 2 - $25/hour, Level 3 - $20/hour and Level 4 - $15/hour.
According to the respective shift, every employee is paid for the real time he works. Taking the example of Worker 1, he will be paid 1:15x$35+6:00x25+2:25x15 = $230
From an employee perspective, I think it's a very good job, very hard but also well paid. From a business perspective, well I'm sure that the company has ensured its earnings and profits.

However, for internal statistics and other calculation, I need to distribute worked time over 6 hours shift (5:00-11:00 for Worker 1) in those levels, as shown in my file.
That's my issue and my difficulty at the present.
Cheers
Ioncila

GTO
04-16-2014, 02:39 AM
Cheers Ioncila :-)

The date still needs to be included if you are working past midnight. See #13.

Mark

Bob Phillips
04-16-2014, 03:02 AM
I would write a UDF, the rules are far too complex to encapsulate in a formula in my view, the UDF would be far more maintainable.

ioncila
04-16-2014, 05:06 AM
I would write a UDF, the rules are far too complex to encapsulate in a formula in my view, the UDF would be far more maintainable.

Can you help me with some orientation?
Thanks
Ioncila

GTO
04-16-2014, 12:20 PM
Hi Ioncila,

Reference the in and out date, would you want these dates in their own columns, or, combined with the in and out times?

Mark

ioncila
04-17-2014, 01:56 AM
Hi Ioncila,

Reference the in and out date, would you want these dates in their own columns, or, combined with the in and out times?

Mark

Hi Mark
As I said in post #16, I need to distribute worked time over 6 hours shift (5:00-11:00 for Worker 1), For that example, 00:15 must be calculated to Level A (05:00-04:45), 1:00 to Level B (12:00-11:00) and 2:25 to Level C (14:25-12:00).

So I'm trying to build it in reference to ranges (00:15 to 00:00-06:00, and so on...) without reference to date.
I hope this means logical.

Thanks
Ioncila

GTO
04-17-2014, 02:17 AM
...
For example, Level 1 is paid $35/hour, Level 2 - $25/hour, Level 3 - $20/hour and Level 4 - $15/hour.
According to the respective shift, every employee is paid for the real time he works. Taking the example of Worker 1, he will be paid 1:15x$35+6:00x25+2:25x15 = $230
From an employee perspective, I think it's a very good job, very hard but also well paid. From a business perspective, well I'm sure that the company has ensured its earnings and profits.

However, for internal statistics and other calculation, I need to distribute worked time over 6 hours shift (5:00-11:00 for Worker 1) in those levels, as shown in my file.
That's my issue and my difficulty at the present.
Cheers
Ioncila


Hi Mark
As I said in post #16, I need to distribute worked time over 6 hours shift (5:00-11:00 for Worker 1), For that example, 00:15 must be calculated to Level A (05:00-04:45), 1:00 to Level B (12:00-11:00) and 2:25 to Level C (14:25-12:00).

So I'm trying to build it in reference to ranges (00:15 to 00:00-06:00, and so on...) without reference to date.
I hope this means logical.

Thanks
Ioncila

I would be happy to be wrong, but discluding the date seems less likely accurate. To do the simple math (subtracting starts from ends) either requires chunking in a bunch of IFs (and making assumptions when the value of the end time (the decimal portion of the date) is less than that of the start time, or, including the date with both start and end times.

FWIW, I would use separate columns.

Mark

EDIT: Above was edited a bit, as my first verbiage was less than stellar in clarity.... (it sucked)

ioncila
04-17-2014, 02:46 AM
I would be happy to be wrong, but discluding the date seems less likely accurate. To do the simple math (subtracting starts from ends) either requires chunking in a bunch of IFs and making assumptions when the value of the end time (the decimal portion of the date) is less than that of the start time.

Mark

That's where I'm stucked (If this, If that,...). So I'm open and following all suggestions