• 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.

separate time in 2 shifts

Status
Not open for further replies.
M

Mamdouh Elfors

Member
Dear All
I have a question

first shift from 8 am to 8 pm
second shift from 8 pm to 8 am

if work starts at 7:16 am on 3/7/2018 and ends at 8:20 am on 5/7/2018, then I want the work hours to be distributed on the two shifts mentioned above. I need to show how many hours will each shift need to work on in order to fulfill the work order (Formula in column D and E ) .

Attached is a worksheet to explain more .

Can anyone help me with this?

Thank you.
 

Attachments

  • separate time in 2 shifts.xlsx
    11.3 KB · Views: 14
Thank you for your reply.
I think you are on the right track. However, when we applied your equations to our input, we got two wrong results:
  1. The order of the shifts becomes (shift2-shift1-shift2-shift1-shift1)
  2. Some values regarding the working time are negative values (negative time).
Attached is the file after we applied our input, you can take a look if you want.

Thanks.
 

Attachments

  • separate time in 2 shifts.xlsb
    47.3 KB · Views: 3
A formula that may help is for the number of hours within a shift

= MEDIAN( clock.off, shift1.start, shift1.end )
- MEDIAN( clock.on, shift1.start, shift1.end )

The effect of the MEDIAN function is to discard any hours worked outside the period of the shift.
 
Mamdouh Elfors
With Your original sample data it works ...
Now, You have added enough variations of data - much better!
It's possible to test better with this data.
Test again ...
 

Attachments

  • separate time in 2 shifts (1).xlsb
    33.4 KB · Views: 10
Separated by formula as described above.
Note: I was not sure how to report hours worked before 8am since they might well be reported as belonging to the previous night shift.
 

Attachments

  • separate time in 2 shifts (PB).xlsb
    35 KB · Views: 10
we have marked some rows that mixes the name of the shifts (shift 1 should be shift 2 and shift 2 should be shift 1). Can you please take a look?
 

Attachments

  • separate time in 2 shifts (2).xlsb
    47.7 KB · Views: 6
I'm a newbie here. I have an almost identical issue but looking at your sheet I can't work out where your reference cells are for the shift start and end times. When I use the MEDIAN function suggested in the earlier post in my formula, I placed the shift times with an absolute cell ref, however now I can't get the formula to return the second shift, or any detail when the shift runs through into a second day.

I have tried with the date time split or joined but no luck either way, any help is welcome and I've uploaded my sheet for reference
 
I've looked at #9. I cant find a reference or formula to show the shift parameters, which I ultimately wish to make dynamic. My shift sample is 09:00-18:00, but I will want to vary this from employee to employee. My goal is to track time at work for zero hours employees, and particularly those who operate during the primary shift. Those who work more than 40+ primary hours per week will be suitable for a full contract, while those who still work enough hours but not all in the primary time span will remain on a zero hours contract while others are not really needed at all.
I'm keen to expand my knowledge with Excel so trying to understand what the formula does is far better than simply accepting a solution. I tried to view formulas on #9 but then got bogged down with the table format. Still vexed!
 
:( Ha Ha, well that certainly explains why I cant find any to learn from!. Yep! I'm really interested in formula to extract the primary shift from the time worked. I laboured over fairly lengthy nested IF, AND formula without getting a reliable result across the sheet, especially when the time at work spanned midnight, and in some cases where the shift continued into a second shift.
I know I can solve the problem on piece of paper for each record but that's defeating the object a bit isn't it?
Are you able to point me in a direction to solve my conundrum?
 
DEAN HOWES
I won't use formulas on sheet for this kind of ...
I would use VBA to get eg that kind of output by press that [ Do It ]
What kind of output You need?
 
nope! missed the overnights completely. Check cell K8 answer should be 9 hours! same for all overnight shifts. I think this came out with the same as previously posted
 

Attachments

  • split date ad time.xlsx
    22.4 KB · Views: 2
VLETM,sorry #9 is not my sheet but contained almost identical problem but with manual solution for original poster. Im looking for formula to learn and automate my sheet
 

Attachments

  • split date ad time.xlsx
    22.4 KB · Views: 3
DEAN HOWES
Why sorry?
There were actually one difference ...
Your version has different lengths for shifts.

I did my kind of sample version ... using, of course 'my layout'.
I add 'test'-sheet
There is [ Do It ]-button
It uses Your 'Sheet1'-data and
creates same kind of layout as #9.
You can filter 'Sheet1' as needed ... after [ Do It ]
... 'test'-sheet would show as 'Sheet1'.
> Questions? Ideas?
Those 'results' would add to 'Sheet1' too...
 

Attachments

  • split date ad time (1).xlsb
    41.9 KB · Views: 4
The problem is that your end time in column G decreases by 1 at midnight which invalidates your time difference formulas. If you instead subtract the start date from the end date/time (so allowing 'times' to go beyond 24 hours) your existing formulas will still work (unless someone clocks-on late one day and works into the day shift the following day).
 

Attachments

  • split date ad time (PB).xlsx
    22.6 KB · Views: 7
VLETM, I can now see the output on 'TEST sheet' as you describe with all the relevant solutions. But I do not yet aspire to VBA, or even simple MACROS and I'm living in the world of formulas. My personal goal here is to learn how to achieve the outcome using formulas if that is at all possible. Thanks so far.
 
Peter, I will try your suggestion however which formula solution should I start with. Column G is a single time which will produce a negative therefore inappropriate time and as you say it is only helpful if there is no late clock on to go into the next day which unfortunately there could be.
 
I quoted column G as being the time element of the clocking off date-time. If you look at my implementation you will see a time that looks the same but actually represents 1 day plus some hours if the worker is still there past midnight.

That change makes the time differences in columns I and M to be always positive. If, as you say, it is possible that you might expect a worker to clock-on before midnight and work past 9:00 then you would need to account for the extra overtime hours worked during the day shift the following day.

I have made such a change in column K but it is not pretty.
 

Attachments

  • split date ad time (PB).xlsx
    23 KB · Views: 21
Status
Not open for further replies.
Back
Top