Matching a time stamp to a shift and identifying the crew....

[caytib]

So, I have two spreadsheets. One has a list of data with a time and date stamp on it. The other has the crew shift schedule on it.

Spreadsheet 1

A B C D
1/1/11 8:00 PM 42 637 11222
1/1/11 11:00 PM 66 499 10978
1/3/11 5:00 AM 27 529 10223
1/3/11 7:00 AM 56 588 11017

These time stamps are from any hour of the day.




Spreadsheet 2

A B C D E F
TIME SHIFT STARTS CREW1 CREW2 CREW3 CREW4 CREW5
1/1/11 06:00 AM D O O N S
1/2/11 06:00 AM D O O N S
1/3/11 06:00 AM O N D S O

(D = Day, N= Night, O = Off, S= Staff Day)

I want to go through the timestamp data and figure out which crew was working. So if I look at line 1 and it is 1/1/11 8:00 PM, the macro or formula can go look at spreadsheet 2, find out that that occurs during 1/1/11 6:00 AM shifts, and that it is Crew D. Once it finds the row with the right crew, I'll copy the 5 cells with the crew info over to spreadsheet one, but I need help with getting it to find the right row. Is this possible?


Any help is appreciated.

 

[gnrboyd]

If your data is sorted in date order and formatted as numeric values as a date (not text), then you can use the VLOOKUP formula. In the example below, the date from the first sheet is looked up in the second sheet and it pulls the corresponding data into sheet 1.

As I stated, the dates in sheet 2 have to be sorted in ascending order or it will return incorrect data. The VLOOKUP formula looks down the date list until it finds a value larger and then returns the the value on the last row before going over. This is why the dates in the 2nd sheet have to be sorted. (The dates in the first sheet can be in any order.)

Hope this helps.

Excel 2002
	A	B	C	D	E	F	G	H	I
1	A	B	C	D	CREW1	CREW2	CREW3	CREW4	CREW5
2	01/01/2011 08:00 PM	42	637	11222	D	O	O	N	S
3	01/01/2011 11:00 PM	66	499	10978	D	O	O	N	S
4	01/03/2011 05:00 AM	27	529	10223	D	O	O	N	S
5	01/03/2011 07:00 AM	56	588	11017	O	N	D	S	O
6
7
8
9
10	TIME SHIFT STARTS	CREW1	CREW2	CREW3	CREW4	CREW5
11	01/01/2011 06:00 AM	D	O	O	N	S
12	01/02/2011 06:00 AM	D	O	O	N	S
13	01/03/2011 06:00 AM	O	N	D	S	O
Sheet1

Cell Formulas
Range	Formula
E2	=VLOOKUP($A2,$A$11:$F$13,2)
E3	=VLOOKUP($A3,$A$11:$F$13,2)
E4	=VLOOKUP($A4,$A$11:$F$13,2)
E5	=VLOOKUP($A5,$A$11:$F$13,2)
F2	=VLOOKUP($A2,$A$11:$F$13,3)
F3	=VLOOKUP($A3,$A$11:$F$13,3)
F4	=VLOOKUP($A4,$A$11:$F$13,3)
F5	=VLOOKUP($A5,$A$11:$F$13,3)
G2	=VLOOKUP($A2,$A$11:$F$13,4)
G3	=VLOOKUP($A3,$A$11:$F$13,4)
G4	=VLOOKUP($A4,$A$11:$F$13,4)
G5	=VLOOKUP($A5,$A$11:$F$13,4)
H2	=VLOOKUP($A2,$A$11:$F$13,5)
H3	=VLOOKUP($A3,$A$11:$F$13,5)
H4	=VLOOKUP($A4,$A$11:$F$13,5)
H5	=VLOOKUP($A5,$A$11:$F$13,5)
I2	=VLOOKUP($A2,$A$11:$F$13,6)
I3	=VLOOKUP($A3,$A$11:$F$13,6)
I4	=VLOOKUP($A4,$A$11:$F$13,6)
I5	=VLOOKUP($A5,$A$11:$F$13,6)

 

[caytib]

You are stinking brilliant! And that is so much easier than the way I was trying to do this. (I have never been good at KISS (Keep It Simple Stupid), I prefer the 'try to do it the hardest way humanly possible until you are banging your head on your desk' method P ).

And it's probably more proof that Excel has evolved faster than I have and it's time to go take an Excel class.

Thank you so much!

 

[gnrboyd]

You are stinking brilliant! And that is so much easier than the way I was trying to do this. (I have never been good at KISS (Keep It Simple Stupid), I prefer the 'try to do it the hardest way humanly possible until you are banging your head on your desk' method P ).

And it's probably more proof that Excel has evolved faster than I have and it's time to go take an Excel class.

Thank you so much!

I'm glad this solution worked for you.

As far as being stinking brilliant.... Well I probably qualify for being stinky from time to time, but NEVER brilliant. But, if you hang out on this web site, you will find many that ARE brilliant. I wish I was 1/10th as smart as some of these folks.

You can probably skip the Excel classes and just go to YouTube and search for Bill Jelan's Excel videos. This guy makes it look so easy. I wish I could just remember the stuff I learn when it comes time to use it.

Good luck!