Differentiate shifts

L

laurentdes

New member
Joined
Nov 3, 2016
Messages
17
Reaction score
0
Points
0

Attachments

  • Version 1.xlsm
    45.3 KB · Views: 15
Last edited by a moderator:
Or would be great to add a column called PH to identify all the public holidays hours falling on PHoliday dates ( 24 HR )
 
Please do not hijack existing threads but create a new one for each question.
If necessary you can add a link to an existing thread.
I will do it for you this time
 
Pecoflyer said:
Please do not hijack existing threads but create a new one for each question.
If necessary you can add a link to an existing thread.
I will do it for you this time
Click to expand...

Merci Beaucoup

Apologies, appreciate your assistance, will make sure next time i won't make same mistake
 
I see in the PH sheet that you have a column A 'Applicable to'; where is the information correlating to that on the Master sheet?
 
p45cal said:
I see in the PH sheet that you have a column A 'Applicable to'; where is the information correlating to that on the Master sheet?
Click to expand...

its not linked to the master sheet . i just want to achieve same concept like ord, night , sat and sun , but i want a PH option added as an array based on date as per list in the PHoliday tab
 
p45cal said:
I see in the PH sheet that you have a column A 'Applicable to'; where is the information correlating to that on the Master sheet?
Click to expand...


the only column linked to the PH sheet is column J which is a vlookup to get the Public Holiday name on the date

What am trying to achieve is get results populated from column K :O where column O is P Holiday hours
The tricky thing is to make sure when someone works from previous day night to Pholiday morning day, we need to capture the hours falling on the PH. Same principle as the night shift .

Column E:H is part of the work done originally. What i want is a tweak -adding PH Column with will give me results as per Column K:eek:View attachment Version 1.xlsmView attachment Version 1.xlsm
 
laurentdes said:
the only column linked to the PH sheet is column J which is a vlookup to get the Public Holiday name on the date
Click to expand...
and
laurentdes said:
The tricky thing is to make sure when someone works from previous day night to Pholiday morning day, we need to capture the hours falling on the PH.
Click to expand...
Exactly! But different states have different holidays as is clear from the PH sheet. We will need to know the state for every row you want public holiday hours for. When working out the public holiday hours when working over midnight, in some states it might be both dates that are public holidays, in others it might be only one, in others the other, or none! If all the rows on a sheet are for the same state then we only need one cell on that sheet to tell the function which state we're in. At first sight, I haven't (so far) been able to get your current expected results without multiple states being involved.
 
p45cal said:
and
Exactly! But different states have different holidays as is clear from the PH sheet. We will need to know the state for every row you want public holiday hours for. When working out the public holiday hours when working over midnight, in some states it might be both dates that are public holidays, in others it might be only one, in others the other, or none! If all the rows on a sheet are for the same state then we only need one cell on that sheet to tell the function which state we're in. At first sight, I haven't (so far) been able to get your current expected results without multiple states being involved.
Click to expand...

Yes you are right. We need an additional Column for state. Instead of having guard number in Column, we can replace it with the State, that would be great

Am tipping the PH will work same as a SUN shift however, the additional variables that will trigger a PH is State and the dates of PH loaded in PH tab. For the PH, may formula can be better rather than user defined function . I will keep working on it and see if i can also find a solution . Am not an expert though :( but i highly appreciate your help
 
As a rule of Thumb, am not planning to work with different states at same time. Am only working on 1 State per sheet .
 
i have attached the workings i did, column M: P are the hours that are PH . instead of having them in 4 columns, i want to add another column after Column H Sun and name it PH . The PH column will have the total hours from M:p . :)
 

Attachments

  • Version 2.xlsm
    172.4 KB · Views: 9
In the attached:

  • Columns Q:T of Master sheet is your manually created expected results
  • Columns K:N my results (conditionally formatted to highlight differences)
  • Columns V:Y a comparison of my results versus your expected results (conditionally formatted to highlight differences)
  • Column O is the state applicable to each row to get your results
  • Column B of the PH sheet has highlights where you had some unusual dates
  • I've altered the format of column B of the Master sheet to show days of the week (only to help visual checking of results)

I have made alterations to the existing ShiftSplit user-defined function:
There is no change to how it's used in columns E:H of the Master sheet.
Here's how it's used in columns K:N:
=IFERROR(ShiftSplit(B10,C10,D10,PH!$A$2:$B$109,O10)*24,"")

Singling out the function itself in that formula:
ShiftSplit(B10,C10,D10,PH!$A$2:$B$109,O10)
you can see an additional 2 arguments;

  • the first new argument being the 2-column range containing the dates and states (in that order),
  • the second new argument being the state for that row.

The second new argument (O10) could be a literal string eg.:
ShiftSplit(B10,C10,D10,PH!$A$2:$B$109,"ACT")
or a reference to a single cell containig the state for the sheet eg.:
ShiftSplit(B10,C10,D10,PH!$A$2:$B$109,$J$1)

These last 2 new arguments are optional; their very presence indicates that the results will be the public holiday aspect of the working hours.

The formula is, as before, an array-entered one, 4 cells across entered in one go.

So far I haven't streamlined the code in the function; if I get time/inclination I will do this.
So now it's over to you to test thoroughly.

There is an outstanding difference between your expected results and mine on row 112; I think that your expected results are awry (you can't expect Sunday public holiday hours on working overnight from Thursday to Friday!).
 

Attachments

  • ExcelGuru7931Version 1.xlsm
    53.8 KB · Views: 7
Last edited:
Hi

thanks for everything . Line 112 was my boubou . you were right , i def got it wrong
I have done a few testings and so far so good. Am very happy with the expected results . Its helpful and i can tell that you have done a great job . :)

Cheers
 
Last edited by a moderator:
Hi When i input times 15:00 to 23:00 , it doesnt detect the PH hours on PH days .Just an example

Regards
 
laurentdes said:
Hi

thanks for everything . Line 112 was my boubou . you were right , i def got it wrong
I have done a few testings and so far so good. Am very happy with the expected results . Its helpful and i can tell that you have done a great job . :)

Cheers
Click to expand...

When end times falls next daY IT works fine, but when end time falls same day its not picking the hours as PH
 
I've only had a quick glance and made a small change; could you test the attached again?
 

Attachments

  • ExcelGuru7931Version 1.xlsm
    53.9 KB · Views: 9
You must log in or register to reply here.
Back
Top