Calculate sub shift hours

[ioncila]

Hi
I'm doing a crosspost
http://www.excelforum.com/excel-gene...fts-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]

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]

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]

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]

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]

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]

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]

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]

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]

@ 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]

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]

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]

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]

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]

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]

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]

Cheers Ioncila :-)

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

Mark

[Bob Phillips]

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]

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]

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