Display Shift and Reporting Date with 3 Overlapping Shifts

[MnCleanSup]

http://www.mrexcel.com/forum/excel-questions/303734-display-shift-formula-based-time-stamp.html

The link above was a start but did not get me where I need to be. So...

I have a problem. I have a workbook full of work order scan offs with start and end timestamps. I need to determine the "reporting day" and "shift" for each scan off. I say "reporting day" because I have 3 shifts (1st shift - 6 am to 2:30 pm, 2nd shift - 2 pm to 10:30 pm, and 3rd shift - 10 pm to 6:30 am) and my "reporting day" starts with 1st shift. My problem is coming up with a way to report 3rd shift (after Midnight) data as the previous day and also finding a way to interpret the data that is recorded when shifts overlap. Would you happen to have any clue as to how I could resolve this issue? I really appreciate the help! I'm thinking something along the lines of referencing the first scan off and last scan off but the overlapping shifts has really got me puzzled!

 

[MnCleanSup]

I also want to account for early and late scan off times (in case the operator was called in to his shift early, or worked after the normal end of his shift

My columns are:

Start Date----------Start Time----------Finish Date----------Finish Time----------Employee Name----------Shift----------Reporting Date----------Processing Start

2/22/16----------23:45----------2/23/16----------4:55----------SH----------*NEED FORMULA*----------*NEED FORMULA*----------(CONCATENATED 'Start Date and Start Time)

*These are my thoughts on formulas for each shift:

1st Shift:
If first instance of "Processing Start" > 5 AM AND last instance of "Processing End" is < 6 PM, then "1st Shift"

2nd Shift:
If first instance of "Processing Start" > 1 PM AND last instance of "Processing End" is < 2 AM the next day, then "2nd Shift"

3rd Shift:
If first instance of "Processing Start" > 9 PM AND last instance of "Processing End" is < 10 AM the next day, then "3rd Shift"


I'm thinking that this might need to refer to the "Employee Name" column in order to get the first and last instances.

*This seems really complicated to me but also able to be solved by one of you wizards out there!

any help is very much appreciated!

 

[jorismoerings]

http://www.mrexcel.com/forum/excel-questions/303734-display-shift-formula-based-time-stamp.html

The link above was a start but did not get me where I need to be. So...

I have a problem. I have a workbook full of work order scan offs with start and end timestamps. I need to determine the "reporting day" and "shift" for each scan off. I say "reporting day" because I have 3 shifts (1st shift - 6 am to 2:30 pm, 2nd shift - 2 pm to 10:30 pm, and 3rd shift - 10 pm to 6:30 am) and my "reporting day" starts with 1st shift. My problem is coming up with a way to report 3rd shift (after Midnight) data as the previous day and also finding a way to interpret the data that is recorded when shifts overlap. Would you happen to have any clue as to how I could resolve this issue? I really appreciate the help! I'm thinking something along the lines of referencing the first scan off and last scan off but the overlapping shifts has really got me puzzled!

Take a look at this:

Book1
	A	B	C
1	Shifts	Start	End
2	1	06:00	14:30
3	2	14:00	22:30
4	3	22:00	06:30
5
6
7	Start	End	Shift
8	14:00	21:30	2
Sheet1

Cell Formulas
Range	Formula
C8	=IF(MEDIAN(A8,B8)=MEDIAN($B$2,$C$2,A8,B8),1,IF(MEDIAN(A8,B8)=MEDIAN($B$3,$C$3,A8,B8),2,3))

 

[MnCleanSup]

Take a look at this:

Book1
	A	B	C
1	Shifts	Start	End
2	1	06:00	14:30
3	2	14:00	22:30
4	3	22:00	06:30
5
6
7	Start	End	Shift
8	14:00	21:30	2
Sheet1

Cell Formulas
Range	Formula
C8	=IF(MEDIAN(A8,B8)=MEDIAN($B$2,$C$2,A8,B8),1,IF(MEDIAN(A8,B8)=MEDIAN($B$3,$C$3,A8,B8),2,3))

Thanks jorismoerings for looking into this!

For clarification....My dataset contains "clock-offs" on work orders, capturing all of the data from that work order including the processing start time and end time for each employee that worked on said part. So an employee might have 5 to 10 different clock-offs in a shift. Part #1 could have been processed from 22:00 to 23:00, Part #2 from 23:00 to 02:00 the next day, Part #3 from 02:00 to 6:30 am, etc. So there is not a single line that captures the "clock-in" and "clock-out" times for the entire shift, only multiple lines of data that make up close to the entire shift. There is data, like lunches and indirect activities like maintenance that could account for the remainder of the shift time. I hope that I haven't confused anyone with this explanation.

 

[MnCleanSup]

I did try your example, jorismoerings, but it did not always show the correct shift. I think part of this was due to the facts that all "clock-offs" did not span the entire shift and that the shifts overlap.

 

[jorismoerings]

Unfortunately i can't help you any further if you are replying in general terms.

If you want the shift to be determined there must be something solid to go on. My assumption was simple: All registered timestamp will always fill one shift. That's what the formula is doing beside one major issue: if start and end times are within the overlapping 30 minutes in between shifts. Because with that basic data you can't determine if the block time belongs to either shift so it need to be dependable on earlier filled rows but ..... that was not what you've asked for.

It would realy help (not just me but anyone who tries to help you in the future) if you post a complete question and not just parts of information. Because your second post has a complete diffrenet mindset than the original OP.
Not a problem but it is confusing.

So to stop any misunderstanding:
Could you share some sample data (as closely aligned to your real data), provide the expected outcome in your sample data and combine it with the question you want to be solved.

Hope this helps.

This way you're providing all information nescessary to have your issue solved.
Hope this helps.