New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Calculate night/day/weekends hours + twist

Formulas, Functions, Formatting, Charts, Data Analysis, etc.

Calculate night/day/weekends hours + twist

Postby Bambi » Thu Apr 21, 2016 12:33 pm

Hey there,

I tried to create a a file that can return me the hours made during a shift. There are 4 différents possibilities:
- day hours (6:30 to 18:30. Max 9h)
- night hours (18:30 to 6:30. Max 9h)
- overtime day (6:30 to 18:30 on weekends and if days hours>9)
- overtime night (18:30 to 6:30 on weekends and if night hours>9)

You can see on the worksheet in the first line the formulas that i came up with but they don't take into account saturdays and sundays. But the idea is there :)

For the twist I would need to substract 30 minutes for lunch break only by checking a box. My problem here is that I don't know how to choose where to substract the 30 minutes. On normal day or night shift it's pretty easy but when they cross each other i just don't know. If y'all have an idea i would appreciate. Mine would be to add another box so I can decide if its a day or night meal but i would prefer only one box. Maybe a formula taking account if time during day or night is the bigger.

Anyway since i will probably have to modify later to add that feature, it would be nice if the 4 différents possibilities weren't linked with each other (unlike what I did haha).

Thanks
  • 0

You do not have the required permissions to view the files attached to this post.
Bambi
Rookie
 
Posts: 3
Joined: Apr 21, 2016
Reputation: 0
Excel Version: 2013

Re: Calculate night/day/weekends hours + twist

Postby Sisyphus » Sat Apr 23, 2016 5:45 am

I wonder if you could take a slightly different approach:-
  1. Define the day shift as well as the night shift. The duration of each shift = End - Start. I presume two daily shifts of 12 hours, Day = 6:30 to 18:30 and Night = 18:30 to 6:30
  2. In this case the hours worked during dayshift would be [End of Dayshift] - {Start time], or De-C8 in your cell G8.
    This could be modified with =MIN(De-C8, 9) so that it wouldn't be more than 9 hours.
  3. The dayshift overtime, accordingly, would be =MAX(De-C8-9,0)
  4. You could work out the nightshift in a similar manner.
The meal time might best be deducted from the larger of the two numbers. That should look somewhat like this.
=MIN(De-C8, 9) - IF(De-C8 > C8-Fi, $O$8, 0) - I didn't test, but I hope you get the drift. You may need to build in a safeguard against the result becoming negative if the guy worked for, say, 20 minutes during the larger of the two shifts. In fact, you need an additional safeguard, in case he worked equal time in both shifts. You do this by using >= in one formula and plain > in the other.

I would use the WEEKDAY() function to determine the day of the week in column B. Depending upon your International Settings, Sunday might be the first day of the week. So, =WEEKDAY(B11) returns 1 and =WEEKDAY(B10) returns 7. This would enable you to construct a formula like =IF(OR(WEEKDAY(B8) = 1, WEEKDAY(B8) = 7), [count all as overtime], [count overtime as normal]). Since the normal time is represented in the above formulas by the number 9, you could end up with something like this. =MIN(De-C8, IF(OR(WEEKDAY(B8) = 1, WEEKDAY(B8) = 7, 9),0)) - also not tested - and construct the MAX formula on the same pattern.

Please expect that I might not be able to return to this forum in time to respond to any query you might have. Other experts are welcome to pick up the thread should you require additional help.
  • 0

Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Re: Calculate night/day/weekends hours + twist

Postby Sisyphus » Sat Apr 23, 2016 7:36 pm

Yesterday I recommended that you calculate strictly as follows.
Total work hours = [WorkEnd] - [WorkStart]
Start shift hours = [ShiftEnd] - [WorkStart]
End shift hours = [WorkEnd] - [ShiftStart]
I urge you to resist the temptation to calculate these 3 items dependent on each other. Calculate each item solely on its own merits, especially, after you have fixed the glitches I shall now point out.

Glitch No. 1 occurs when the end of the work is on the next day. Now [WorkEnd] - [WorkStart] will produce a negative result. Provided nobody works for longer than 24 hours you can simply add a day to your formula, like,
=[WorkEnd] - [WorkStart] + IF([WorkEnd] < [WorkStart], 1, 0)
to be embedded into your MOD formula. Remember that you must enter a start at midnight as 00:00 hours for this to work.

Glitch No. 2 occurs when somebody starts work on a Friday or Sunday and continues into the next day. In such cases normal work hours and weekend work hours should not be mixed. To solve this problem I, first of all, recommend to you to construct formulas which will work in every case. Don't design one formula to work in one case and another to work in another. Use the method demonstrated above. If a case is true, add (or deduct) something, otherwise not.

The second recommendation I have to make is to start by creating separate columns for weekend work and, perhaps, even for overtime and normal time. This method will enable you to create correct formulas which will produce zero wherever they should. Later, if you want to combine the columns, you can just concatenate the existing formulas with + signs. For better readability you can always enclose formula blocks in parentheses. Excel will resolve the calculations within parentheses before executing the commands between them.

With that said, you should now calculate the total time from (StartWork to Midnight) + (Midnight to EndWork). This looks simple, but it isn't unless you make it so. For example, there may not be any midnight within the specified time. Then, either the first or the second section may be overtime or weekend work time, meaning, it should either be omitted or not, depending upon whether you calculate weekend or normal work time. Just apply the principles and methods I have already told you patiently to each of the 7 possible combinations of the above schema. The result will be quite a long formula which, however, you will be able to copy unchanged vertically and with very minor changes horizontally. Good luck.
  • 0

Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Re: Calculate night/day/weekends hours + twist

Postby Bambi » Mon Apr 25, 2016 9:06 am

Wow thanks for all the input. I will try to do that this week ! I will return with my results and/or glitch as soon as possible :P

Anyone is still welcome to comment !
  • 0

Bambi
Rookie
 
Posts: 3
Joined: Apr 21, 2016
Reputation: 0
Excel Version: 2013

Re: Calculate night/day/weekends hours + twist

Postby Bambi » Mon May 02, 2016 10:15 am

Thanks to your help I think I finally did it! I used another method with the median I found on an other website and used the tricks you gave me.

Now here is the file. I tested it out but if some of you want to help me make sur there's no "Glitch".

P.S. I know if I check the meal and there's no time I have a negative but I assume this will never happen.

Thnaks again.
  • 0

You do not have the required permissions to view the files attached to this post.
Bambi
Rookie
 
Posts: 3
Joined: Apr 21, 2016
Reputation: 0
Excel Version: 2013


Return to General Excel Questions

Who is online

Users browsing this forum: No registered users and 67 guests