Calculate evening- and night-shift hours from working time

[jeppis]

Is there any function to calculate this:

- starting time 05:12 am (for example in A1)
- ending time 23:38 pm (for example in B1)

And then the problem:
How do I separate evening and night working time what has been done between the total working time mentioned above?

1. night-shift time (between 00:00am-06:00am and 23:00pm-24:00pm)
2. evening-shift time (between 18:00pm-23:00pm)

Any suggestions?

 

[Tetra201]

While Barry is off-line, try these formulas:

Day shift
=(A3>B3)*MEDIAN(0,B3-1/4,7/12)+MAX(0,MIN(5/6,B3+(A3>B3))-MAX(1/4,A3))

Evening shift
=(A3>B3)*MEDIAN(0,B3-5/6,1/4)+MAX(0,MIN(1,B3+(A3>B3))-MAX(5/6,A3))

Overnight shift
=B3-A3+(A3>B3)-C3-D3

 

[Riddler77]

Hi Tetra,

Thanks so much for your response.

Firstly, I have updated the other shift formulas to reflect the difference in hours from the original post -

Day Shift 6:00 -> 20:00

=24*((A3>B3)*MEDIAN(0,B3-1/4,1/2)+MAX(0,MIN(20/24,B3+(A3>B3))-MAX(1/4,A3)))

Evening Shift 20:00 -> 0:00

=???

Your suggestion for Overnight Shift works correctly when the hours are only within the Overnight shift (ie 0:00 to 6:00) however, when the hours carry over from the previous shift or previous day, or alternatively carry into the following day shift it returns unexpected results.

For example -

01:00 -> 04:00 returns 3.0 (Correct)
01:00 -> 07:00 returns -18 (Incorrect, should return 5))
23:00 -> 4:00 returns -19 (Incorrect, should return 4))

Thanks So Much!

 

[Tetra201]

The formulas from Post #11 return your desired results: 3:00, 5:00, and 4:00.

Apparently, you tried to multiply all three formulas by 24. While this works for the first two of them, that's not the right way to do it for the third one.

The correct formula is

=24*(B3-A3+(A3>B3))-C3-D3

 

[Riddler77]

HI Tetra,

You are a legend, thanks so much for your assistance with this matter.
This will save me an amazing amount of time!
The above works correctly. Now you have solved it for me it is rather obvious, however, I was unable to solve it after hours of frustrating trying!

Thanks again!

 

[Tetra201]

You are welcome.

 

[Ben F]

Hi Tetra,

Thanks so much for your response.

Firstly, I have updated the other shift formulas to reflect the difference in hours from the original post -

Day Shift 6:00 -> 20:00

=24*((A3>B3)*MEDIAN(0,B3-1/4,1/2)+MAX(0,MIN(20/24,B3+(A3>B3))-MAX(1/4,A3)))

Evening Shift 20:00 -> 0:00

=???

Your suggestion for Overnight Shift works correctly when the hours are only within the Overnight shift (ie 0:00 to 6:00) however, when the hours carry over from the previous shift or previous day, or alternatively carry into the following day shift it returns unexpected results.

For example -

01:00 -> 04:00 returns 3.0 (Correct)
01:00 -> 07:00 returns -18 (Incorrect, should return 5))
23:00 -> 4:00 returns -19 (Incorrect, should return 4))

Thanks So Much!

Hi Guys,

Ive been trying to solve this issue for some weeks now and this almost gets me there. I do not undertsand how using median, max and min supported by fractions makes this work. Would love it if you could explain it to me?

Most importantly i just need the 2 peramitors 06:00 - 22:00 Basic 22:00 -06:00 Premimium. Would be very greatfull if you could tell me how to adapt the formlulas to achaive this?

Many thanks Ben

 

[YS888]

@barryhoudini @Tetra201 I have been trying to apply your formula to the following as it is the only formula on the internet which suits my requirements. However I cannot for the life of me figure out how to separate.

1. Day shift times: 6am to 20:00
2. Evening shift times: 20:00pm23:59pm
3. Night shift times: 00:00am-05:59am
4. Overnight shift times: 10pm to 6am

Based on previous posts, day shift formula works fine.
The others are creating issues for me because I am at a loss at how to change the shift boundary times in the formulas.

My sheet is basically identical to Barry's but with an additional "Overnight" column, where I've used an IF formula to return "overnight" rather than the hours. However this formula still counts "evening" & "night" hours as well, which I don't want.

I'd be grateful for your assistance with this (desperate in fact). I've spent so many hours on these formulas, it's getting beyond a joke lol