[SOLVED] Evaluate a time slot if it is in a range

It would help if you attached a sample Excel workbook, so we can see exactly how your data is laid out. The yellow banner at the top of the screen explains how you can do this.

Hope this helps.

Pete

Hi,

Added two helper cells, G1=22:00 and H1=06:00

Enter the below formula in F2 and copy down till required range.

Formula:

Please Login or Register  to view this content.

Please refer to the attached file for the completed/finished result.

hi there ,
first of all thenk you for the help.
there is an issue if the start time is at 05:00 and the end time is at 13:00 the result is 0 but it should be 1

any idea about that

Could you please inform me of all duty slots or time slots that occur between 22:00 and 06:00?

there are no specific standart slots.
the only standar is that if the working slot is some time betewen 22:00 - 06:00 then that period is in the night shift.

Try this,

E2
=IF(D2>C2,D2-C2,(1-C2)+D2)*24

F2
=IFERROR(1/(1/(IF(D2>C2, (MEDIAN(C2,D2,1)-MEDIAN(C2,D2,22/24))+ (MEDIAN(C2,D2,6/24)-MEDIAN(C2,D2,0)), (1-MEDIAN(C2,1,22/24))+ MEDIAN(0,D2,6/24) )*24)),"-")

copied down.

hi there again,

it looks ok now but i have a question if i may.

what is the perpose of this 1/(1/(IF(D2>C2,...

thank all for the help

it works as i was looking for.

You are welcome.

Or

Please Login or Register  to view this content.

Originally Posted by JEC.

Or

Please Login or Register  to view this content.

Please check, there is an issue if the start time is at 05:00 and the end time is at 13:00 the result is 00:00 but it should be 01:00.

And now?

Please Login or Register  to view this content.