Saturday, February 22, 2014

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/1408:00:00 AMCampbell JasonIn
Sun 2/16/1411:30:00 AMCampbell JasonOut
Sun 2/16/1412:30:00 PMCampbell JasonIn
Sun 2/16/1404:30:00 AMCampbell JasonOut
Mon 2/17/1411:00:00 PMCampbell JasonIn
Tue 2/18/1401:30:00 AMCampbell JasonOut
Tue 2/18/1402:00:00 AMCampbell JasonIn
Tue 2/18/1408:00:00 AMCampbell JasonOut

I have also created a time sheet that looks similar to this:
EmployeeCampbell Jason
DateSun 2/16/14Mon 2/17/14Tue 2/18/14
In08:00:00 AM11:00:00 PM
Out11:30:00 AM01:30:00 AM
In12:30:00 PM02:00:00 AM
Out04:30:00 AM08: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
---------------------------------------------------------------------------------------------------------------------------------------------