• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

The total hours worked must be separated into "Day shift", "Evening Shift", and "Overnight Shift".

A

arnaldo100

New Member
Hi People,

Please I need help with the following issue: I need to automatically using ms excel formulas, distribute the total hours worked in a week, lets say 8 hours, and this total of hours needs to be allocated in the different shifts; Day shift, Evening shift, and Overnight shift. See details below. Also attached spredsheet.

  1. Day shift: 6 am - 6 pm
  2. Evening shift: 6 pm - 12 am
  3. Overnight shift: 12 am - 6 am
Thanks, in advance
Arnaldo
 

Attachments

  • shift work_Payroll.xlsx
    10.9 KB · Views: 8
This is a 365 solution. I hope someone else comes up with something more straightforward!
Code: 
= LET(
    adjOut,     timeOut + (timeIn > timeOut),
    allocatedϑ, MAP(
        timeIn,
        adjOut,
        LAMBDA(in, out,
            LAMBDA(
                SORT(HSTACK(out, shiftChanges), , , TRUE) -
                    SORT(HSTACK(in, shiftChanges), , , TRUE)
            )
        )
    ),
    firstDay,  MAP(allocatedϑ, LAMBDA(ϑ, INDEX(ϑ(), 1, 2))),
    evening,   MAP(allocatedϑ, LAMBDA(ϑ, INDEX(ϑ(), 1, 3))),
    night,     MAP(allocatedϑ, LAMBDA(ϑ, INDEX(ϑ(), 1, 4))),
    secondDay, MAP(allocatedϑ, LAMBDA(ϑ, INDEX(ϑ(), 1, 5))),
    HSTACK(firstDay + secondDay, evening, night)
  )
1720390345451.png
 

Attachments

  • shift work_Payroll.xlsx
    17.7 KB · Views: 4

arnaldo100​

There could be some interesting combinations of times
... even without missing values.
It could solve this way too (by very small steps).
 

Attachments

  • shift work_Payroll.xlsx
    18.5 KB · Views: 5
@vletm
Interesting to compare the two. Both adjust the interval if the time out is earlier than the time in. Instead of MIN(end)-MAX(start) I use an array SORT, but the goal is the same. Instead of helper ranges, I use LET variables to hold intermediate values.

I hit problems processing the list because, at present, Excel cannot cope with the result which is an array of arrays. I use thunks to overcome that issue. These may be routine for me, but they are difficult to explain and shouldn't be needed. Much of the simplicity introduced by array processing is lost at that point.
 

Peter Bartholomew​

If there would be more normal data .. means (too) many missing or invalid values ... this could be more realistic and interesting case.
I won't normally even try to solve something this kind of case with ... formulas.
Of course, those my used 'step-by-step' formulas could 'combine' but .. maybe without that, this is more readable.
I skipped to compare those other my sample values, which should work too.
 
@vletm
Confession time! I appended your test data to the OP cases and it demonstrated that my formula was incorrect. I hadn't included night hours worked before 06:00 in the result as reported (I assumed it to be zero).
Code: 
= LET(
    adjOut,      timeOut+(timeIn>timeOut),
    allocatedϑ,  MAP(timeIn, adjOut, LAMBDA(in,out,
      LAMBDA(
        SORT(HSTACK(out,shiftChanges),,,TRUE)
        - SORT(HSTACK(in,shiftChanges),,,TRUE)
      )
    )),
    firstNight,  MAP(allocatedϑ, LAMBDA(ϑ, INDEX(ϑ(),1,1))),
    firstDay,    MAP(allocatedϑ, LAMBDA(ϑ, INDEX(ϑ(),1,2))),
    evening,     MAP(allocatedϑ, LAMBDA(ϑ, INDEX(ϑ(),1,3))),
    secondNight, MAP(allocatedϑ, LAMBDA(ϑ, INDEX(ϑ(),1,4))),
    secondDay,   MAP(allocatedϑ, LAMBDA(ϑ, INDEX(ϑ(),1,5))),
    HSTACK(firstDay+secondDay, evening, firstNight+secondNight)
  )
Hopefully that is at least correct!
 

Attachments

  • shift work_Payroll.xlsx
    20.2 KB · Views: 3
Peter Bartholomew said:
This is a 365 solution. I hope someone else comes up with something more straightforward!
Code: 
= LET(
    adjOut,     timeOut + (timeIn > timeOut),
    allocatedϑ, MAP(
        timeIn,
        adjOut,
        LAMBDA(in, out,
            LAMBDA(
                SORT(HSTACK(out, shiftChanges), , , TRUE) -
                    SORT(HSTACK(in, shiftChanges), , , TRUE)
            )
        )
    ),
    firstDay,  MAP(allocatedϑ, LAMBDA(ϑ, INDEX(ϑ(), 1, 2))),
    evening,   MAP(allocatedϑ, LAMBDA(ϑ, INDEX(ϑ(), 1, 3))),
    night,     MAP(allocatedϑ, LAMBDA(ϑ, INDEX(ϑ(), 1, 4))),
    secondDay, MAP(allocatedϑ, LAMBDA(ϑ, INDEX(ϑ(), 1, 5))),
    HSTACK(firstDay + secondDay, evening, night)
  )
View attachment 87592
Click to expand...
Thanks, Peter
Appreciated your effort
 
Peter Bartholomew said:
@vletm
Confession time! I appended your test data to the OP cases and it demonstrated that my formula was incorrect. I hadn't included night hours worked before 06:00 in the result as reported (I assumed it to be zero).
Code: 
= LET(
    adjOut,      timeOut+(timeIn>timeOut),
    allocatedϑ,  MAP(timeIn, adjOut, LAMBDA(in,out,
      LAMBDA(
        SORT(HSTACK(out,shiftChanges),,,TRUE)
        - SORT(HSTACK(in,shiftChanges),,,TRUE)
      )
    )),
    firstNight,  MAP(allocatedϑ, LAMBDA(ϑ, INDEX(ϑ(),1,1))),
    firstDay,    MAP(allocatedϑ, LAMBDA(ϑ, INDEX(ϑ(),1,2))),
    evening,     MAP(allocatedϑ, LAMBDA(ϑ, INDEX(ϑ(),1,3))),
    secondNight, MAP(allocatedϑ, LAMBDA(ϑ, INDEX(ϑ(),1,4))),
    secondDay,   MAP(allocatedϑ, LAMBDA(ϑ, INDEX(ϑ(),1,5))),
    HSTACK(firstDay+secondDay, evening, firstNight+secondNight)
  )
Hopefully that is at least correct!
Click to expand...
Peter Bartholomew said:
@vletm
Confession time! I appended your test data to the OP cases and it demonstrated that my formula was incorrect. I hadn't included night hours worked before 06:00 in the result as reported (I assumed it to be zero).
Code: 
= LET(
    adjOut,      timeOut+(timeIn>timeOut),
    allocatedϑ,  MAP(timeIn, adjOut, LAMBDA(in,out,
      LAMBDA(
        SORT(HSTACK(out,shiftChanges),,,TRUE)
        - SORT(HSTACK(in,shiftChanges),,,TRUE)
      )
    )),
    firstNight,  MAP(allocatedϑ, LAMBDA(ϑ, INDEX(ϑ(),1,1))),
    firstDay,    MAP(allocatedϑ, LAMBDA(ϑ, INDEX(ϑ(),1,2))),
    evening,     MAP(allocatedϑ, LAMBDA(ϑ, INDEX(ϑ(),1,3))),
    secondNight, MAP(allocatedϑ, LAMBDA(ϑ, INDEX(ϑ(),1,4))),
    secondDay,   MAP(allocatedϑ, LAMBDA(ϑ, INDEX(ϑ(),1,5))),
    HSTACK(firstDay+secondDay, evening, firstNight+secondNight)
  )
Hopefully that is at least correct!
Click to expand...
Peter Bartholomew said:
@vletm
Confession time! I appended your test data to the OP cases and it demonstrated that my formula was incorrect. I hadn't included night hours worked before 06:00 in the result as reported (I assumed it to be zero).
Code: 
= LET(
    adjOut,      timeOut+(timeIn>timeOut),
    allocatedϑ,  MAP(timeIn, adjOut, LAMBDA(in,out,
      LAMBDA(
        SORT(HSTACK(out,shiftChanges),,,TRUE)
        - SORT(HSTACK(in,shiftChanges),,,TRUE)
      )
    )),
    firstNight,  MAP(allocatedϑ, LAMBDA(ϑ, INDEX(ϑ(),1,1))),
    firstDay,    MAP(allocatedϑ, LAMBDA(ϑ, INDEX(ϑ(),1,2))),
    evening,     MAP(allocatedϑ, LAMBDA(ϑ, INDEX(ϑ(),1,3))),
    secondNight, MAP(allocatedϑ, LAMBDA(ϑ, INDEX(ϑ(),1,4))),
    secondDay,   MAP(allocatedϑ, LAMBDA(ϑ, INDEX(ϑ(),1,5))),
    HSTACK(firstDay+secondDay, evening, firstNight+secondNight)
  )
Hopefully that is at least correct!
Click to expand...
It's working, thanks
 
arnaldo100 said:
Thanks, makes sense
Click to expand...
vletm said:

Peter Bartholomew​

If there would be more normal data .. means (too) many missing or invalid values ... this could be more realistic and interesting case.
I won't normally even try to solve something this kind of case with ... formulas.
Of course, those my used 'step-by-step' formulas could 'combine' but .. maybe without that, this is more readable.
I skipped to compare those other my sample values, which should work too.
Click to expand...
Hi,
The issue is been solved in another Excel Forum, I just want to say, thanks for your help, really appreciated!
Arnaldo
 
Peter Bartholomew said:
@vletm
Confession time! I appended your test data to the OP cases and it demonstrated that my formula was incorrect. I hadn't included night hours worked before 06:00 in the result as reported (I assumed it to be zero).
Code: 
= LET(
    adjOut,      timeOut+(timeIn>timeOut),
    allocatedϑ,  MAP(timeIn, adjOut, LAMBDA(in,out,
      LAMBDA(
        SORT(HSTACK(out,shiftChanges),,,TRUE)
        - SORT(HSTACK(in,shiftChanges),,,TRUE)
      )
    )),
    firstNight,  MAP(allocatedϑ, LAMBDA(ϑ, INDEX(ϑ(),1,1))),
    firstDay,    MAP(allocatedϑ, LAMBDA(ϑ, INDEX(ϑ(),1,2))),
    evening,     MAP(allocatedϑ, LAMBDA(ϑ, INDEX(ϑ(),1,3))),
    secondNight, MAP(allocatedϑ, LAMBDA(ϑ, INDEX(ϑ(),1,4))),
    secondDay,   MAP(allocatedϑ, LAMBDA(ϑ, INDEX(ϑ(),1,5))),
    HSTACK(firstDay+secondDay, evening, firstNight+secondNight)
  )
Hopefully that is at least correct!
Click to expand...
Hi Peter,
The issue has been solved in another Excel Forum to my entire satisfaction. I just want to say, thanks for your help, I really appreciated!
Arnaldo
 

arnaldo100​

Please, reread Forum Rules - those are for You too.

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
 
vletm said:

arnaldo100​

Please, reread Forum Rules - those are for You too.

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
Click to expand...
Hi vletm,
Thanks, good to learn.
Please find attached the spredsheet which includes the solution for the issue that I got from the forum.
Arnaldo
 

Attachments

  • 2024-07-08 AL distribute hours among shifts (1).xlsx
    18 KB · Views: 2
If there would be more normal data .. means (too) many missing or invalid values ... this could be more realistic and interesting case.
I won't normally even try to solve something this kind of case with ... formulas.
Of course, those my used 'step-by-step' formulas could 'combine' but .. maybe without that, this is more readable.
I skipped to compare those other y sample values, which should work too.
Click to expand...
@vletm
You prompt an interesting thought that the array formulas that I use could be more vulnerable to bad data than the traditional ranges of scalar formulas. I probably should pay more attention to checking the formulas against missing or erroneous data. In the present case, the errors typically appear in the analysis of the specific shift that has the missing data. The most likely occurrence is that time-in is recorded whilst time-out is blank. In such a case, adjusting the time-out to
Code: 
adjOut, IF(ISBLANK(timeOut), timeIn, timeOut+(timeIn>timeOut)),
will record zero hours for the shift, which is probably better than assuming the worker stayed to midnight!
 

Peter Bartholomew​

...hmm? Basically all inputs can be invalid.
TimeIn or/and TimeOut missing.
TimeIn or/and TimeOut are 'zero'... 'zero' can be real values too.
TimeIn and TimeOut are in 'wrong' shift.
If there are fixed shifts - how much gaps can be with TimeIn and TimeOut?
... somewhere none - somewhere something else.
Between TimeIn and TimeOut can be over 24hrs or (much) more.
... that's why, there should always be datepart too.
If there are any 'mistakes' then those times cannot solve!
... those should give a note and fix before those could use.
If workingtime lasts over midnight then there will be to parts - one before and other after - of course.
If 'weeklyworking' and previous weeks the last shift ends the 1st day morning ... then there will be to parts too - of course.
.. as well as if the last shift starts the last day and it will end next 'weeks' morning.
... same case if use 'monthly'.
Normally, these calculations are related with money - every 'penny' counts.
Of course, it's possible to make these with formulas - of course.
... maybe I missed something.
BUT. If there are clear rules then many things goes smooth ... other ways - no!
 
Hi @vletm
I agree that the use of datetimes would remove much of the ambiguity. I might trap some obviously invalid data but there are limits as to how far I wish to pursue error checking. My interest is in testing and demonstrating techniques using dynamic array formulas and excessive error checking would mask the underlying simplicity of the solutions. Were I under contract to create a robust solution, that would be a different matter!
 

Peter Bartholomew​

Some error checking could do before actual calculation
... with different 'formula' ... shorter 'formula'.

One basic possible mistake with above solutions are ...
... it should always solve one day values.

'TimeIn' 08:00 & 'TimeOut' 17:00 could mean
a) 08:00-17:00
b) 00:00-08:00 and 17:00-24:00
c) 17:00-24:00 and for next day 00:00-08:00

If there is 'only' 'TimeIn' ... 08:00 - it could mean
a) 00:00-08:00
b) there is missing 'TimeOut'
c) 'TimeOut' will find from next day
.. and something same with 'TimeOut'.

Above means that 'TimeIn' and 'TimeOut' can be written/missed in both orders.
It should be known - what are possible values?
 
You must log in or register to reply here.
Back
Top