yogi_Create An In Out Time Sheet For WorkDays By Employee From A Database of Date_Time_Employee_In/Out

                                         Google Spreadsheet   Post  #1547
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-23-2014
post by JFC111 (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/7uU-3W8fP-A)
Query if certain conditions are met

Hi, I am having trouble with this. I have included a sheet which has the formulas, and how I think they should work, but they don't. Thanks for your help.

I have created a database that is continually updated, this is a simplified example:

Sun 2/16/14	08:00:00 AM	Campbell Jason	In
Sun 2/16/14	11:30:00 AM	Campbell Jason	Out
Sun 2/16/14	12:30:00 PM	Campbell Jason	In
Sun 2/16/14	04:30:00 AM	Campbell Jason	Out
Mon 2/17/14	11:00:00 PM	Campbell Jason	In
Tue 2/18/14	01:30:00 AM	Campbell Jason	Out
Tue 2/18/14	02:00:00 AM	Campbell Jason	In
Tue 2/18/14	08:00:00 AM	Campbell Jason	Out

I have also created a time sheet that looks similar to this:

Employee	Campbell Jason
Date	Sun 2/16/14	Mon 2/17/14	Tue 2/18/14
In	08:00:00 AM	11:00:00 PM
Out	11:30:00 AM	01:30:00 AM
In	12:30:00 PM	02:00:00 AM
Out	04:30:00 AM	08:00:00 AM
In
Out

All I need is for the time to be populated into the correct location. Here is some detail below.

1. The database and the time sheet will be in the same file, but on different tabs. In the shared file I have copied them to the same tab while trying to figure it out.
2. To use this spreadsheet an employee is selected from a validated list
3. I want the time sheet to populate with the correct time based off of 3 different criteria and 1 equation
• The employee name
• The date
• In or out
• Is the time being populated after the prior recorded time with the same In/Out value
4. We have night shifts that go into the next day, I would like them to be placed together if possible(see red highlighted time) if not then my time value calculations need to be tweaked(they currently work but are not included in the shared file)

https://docs.google.com/a/californiansb.com/spreadsheets/d/1UL86SjgVmoB_a0SdyK3VMasad_takZ_z5epi1ZZflUk/edit#gid=0

---------------------------------------------------------------------------------------------------------------------------------------------