calculate working shifts per day from monthly report

OK.. Just when I thought I'm attaining expert status I hit the wall

This is my problem... I have table and need to take out montly total for each worker...

Now...
Each hours in day have own factor. (I need total hours per day but for illustration)...

So when worker works day shift from 8:00 to 16:00 it's easy... 8 hours
When works from 8:00 to 20:00 it's 8 hours + 4 afternoon hours
When works from 20:00 to 8:00 it's 2 afternnoon hours + 8 night hours + 2 day hours

Aditional problem is when day intercept holliday or sunday when that factors need to be included (if holliday is at sunday then it's like holliday).

Here is some attachment:

Book1.xls

I've also added last day of previous month and first day of next month because of night shifts than need to be calulcated. Therefore correct number of hours is 168 and not 188.

Below I calculated manually those numbers wich I want to be automated...

Also.. This is table I get.. If it's easier to make it somehow else, OK by me. And any number of aditional columns is not problem...

*BUMP*

A further info: to not be missunderstood.. I don't need D column. It's just an example here how to divide hours per day and per shift...

I need total of it...

I was just looking at this zbor...

Realistically I would advise use of UDF (VBA) rather than trying to calculate via native functions esp. if you have a large number of ee's listed horizontally (row 1) as I would say native functions would warrant a number of helper columns (duped for each ee)

On a final note - do you have a range specified listing public holiday dates ?

Yes. I'll create list of hollidays on other sheet...

So, it's hard through functions? And I thought it's just me

zbor, if you can't think of a way to do it easily with native functions (given your proficiency) then I'd say it's likely that if there is a way it's going to be very complicated ...

my normal rule of thumb is that if I spend more than 10 minutes trying to think of a way to do it simply with native functions and fail then I revert to UDF based approach for sake of simplicity and sanity.

I do have one major question...

Given Sunday rate (1,2) is less than the Night Shift rate (1,5) using your example of Sat 5th Dec: night_shift_12: 20:00 - 08:00

shouldn't the breakdown of the hours be such that you have:

2 hours @ Reg
8 hours @ O/N (1,5)
2 hours @ Sun (1,2)

ie the over night rate should supercede the Sunday rate for period 00:00-06:00 ?

In your example your showing

2 hours @ Reg
2 hours @ O/N (1,5)
8 hours @ Sun (1,2)

...surely you should use the greatest of all applicable rates at all times else the ee loses out by working overnight shift on a Sunday -- they in fact get paid less than they would on any other day doing the same shift.

Sunday are Holliday are added (multiplied) to current shifts (day, afternoon, night) unless Holliday is on Sunday (then only Hollyday calculated)...

Factors are just for example. Basic problem is to divide on Holliday, Sunday, Night etc... (then can be easily multiplied just last SUM).


That's why on saturday -> sunday:

20:00-8:00

2 afternoon (20-22)
2 night (22-24)
and 8 sunday (because is same wage).

sunday -> monday:

20:00-8:00

4 sunday (20-24)
6 night (24-06)
2 day (06-08)


Instead of regular day:

20:00-8:00

2 afternoon (20-22)
8 night (22-06)
and 2 day (06-08)

zbor, I understand what you want to do I just don't follow / agree with the logic.

you state:

Originally Posted by zbor

That's why on saturday -> sunday:

20:00-8:00

2 afternoon (20-22)
2 night (22-24)
and 8 sunday (because is same wage).

the above (in red) contradicts your file where you state night shift rate is 1,5 and Sunday rate is 1,2 ... hence my question regards the underlying logic.

If the Sunday rate were 1,5 I would agree with you, however, per your file (at least) it is not... please clarify the appropriate rates.

EDIT:

I appreciate you're saying that "factors are examples" but they are of critical importance ....
Should it ever prove to be the case that either of Sun/PH Factors are less than the Max of Mon-Sat "standard" Factors then you must split/calculate based on the Max Factor given the Max rate should always be applied (irrespective of other conditions).

day = 1
night = 1,5

day on sunday = 1*1,2 = 1,2
night on sunday = 1,5*1,2=1,8

Hmmm.. I see now where I get lost

Let me check and I'll write back

Ah, OK, I think we can put this down to the fact that I misinterpreted and assumed by Factors you meant rates whereas in reality you meant Factors! Apologies on my part.

Let state new numbers: Holliday =2 and Sunday =1,8

No day, night etc on it... whole Holliday/Sunday is same...

I'm looking at this in bits and pieces zbor...

The attached represents a WIP which I believe (at this time) generates the correct results [see C38:C43] albeit in an inefficient / poorly coded manner ...

I've added a proof (G:O) to back up the output of the UDF.

I concede that

a) I've not tested in depth
b) the code is ugly and can with more thought be greatly condensed

I will revisit later today hopefully.

(note in the attached 26th Dec is deemed a public holiday per the listing on Info sheet)

EDIT: I just noticed that there would be an issue with existing UDF were any shift to intersect the overnight hours without actually being an overnight shift (ie concluding post 22:00 (pre midnight) or commencing pre 06:00) - that would need to be added

DonkeyOte, thanks for you effort but I think this isn't good approach (I mean on my example, not your solution).

How about this? I add New and New2 columns:

New is referenced on start and end time
New2 is referenced on duration

Instead of inputs in column C maybe column Dor E would be better approach.

However, my knowledge is limited at this point (an idea) and I can't go further and make this work but I'm hoping maybe you can get some idea

712347_zbor(1).xls

Forgot to say, column N a=afternoon, d=day, n=night

and to mention again: only total is important, columns D,E are just helper in this case

zbor, at this stage I'm afraid I bow out - the UDF did what you requested (populated rows 38:43) based on your present approach - I'm not really sure what you're trying to change at this point.

If you wish to totally revise your approach such that you don't need a UDF etc then I would suggest you perhaps close this thread (mark solved) and start afresh such that others review and offer their own insight.

The complexity you face is not simply establishing start & end times (itself relatively trivial as outlined by formulae in G/H) but determining whether the shifts coincide with Sundays & Public Holidays - this is further complicated where a shift is split over two days.

Actually, it seems to work perfectly... I just need more time to figure some things out and to test it...

THX

Stuck at the end...

I've adopted most of things but when I've translated shifts it doesn't work anymore...

And I can't find within code where it takes it :$

NOC.zip

Since I don't know would there be any lost during our letters maybe would be better to tell me what to change within code.

THX

I've only looked at this quickly but in essence I suspect the issue is related to the fact that a number of the shifts listed on the first sheet do not exist within your _Shifts named range.

As a result the UDF "falls over" when trying to establish start/end times of these shifts...hence returning #VALUE! error
(to confirm this you can copy D21 as value over D22:D37 and you will get a result).

In terms of handling - should "shifts not listed" be treated as 0 hours ?

(also the formulae in the matrix should probably used a fixed reference for the Null test)

Correct... When I pulled down names didn't notice that xy_08 went to _09, 10 etc...

I'll keep error for not listed... In that case shifts MUST be entered...

THX.. Let me check further

Y... Work now Great.. THX

Slightly error (hope you'll remember it quickly ....

Check 1st sheet user: LAST NAME 4...

Now, It say like:

Please Login or Register  to view this content.

Whiule it should be:

Please Login or Register  to view this content.

It take 8 hours from night shift of the 25.12.2010 an add it to both: Holliday and Sunday while it should add it only at Holliday (More valuable than Sunday).

Not really tested but perhaps:

Please Login or Register  to view this content.

I suspect you need to add similar logic to 3, 4 & 5 ie add a 2nd test to ensure boolPH = False
[boolPH: Boolean indicating whether Date being iterated (lngDate) is public holiday]

Got it, thx... I think it's OK now... The days of testing are to come