V look up substitute for data with multiple arguments that fall within certain timelines

[nepsyde]

Hey there,

I was wondering if somebody could help me. I have a time card data excel sheet for all restaurant workers that has their clock in & clock out time, their job title and their total hours for one particular day. For example:

Time Card Sheet (Sheet 1)
[TABLE="width: 500"]
<tbody>[TR]
[TD]Department[/TD]
[TD]Clock In[/TD]
[TD]Clock out[/TD]
[TD]Total Hours[/TD]
[/TR]
[TR]
[TD]505 Prep[/TD]
[TD]10/24/2018 1:07:00 PM[/TD]
[TD]10/24/2018 4:34:00 PM[/TD]
[TD]3.45[/TD]
[/TR]
[TR]
[TD]505 Prep[/TD]
[TD]10/24/2018 2:01:00 PM[/TD]
[TD]10/24/2018 5:23:00 PM[/TD]
[TD]3.37[/TD]
[/TR]
[TR]
[TD]512 Runner[/TD]
[TD]10/24/2018 3:00:00 PM[/TD]
[TD]10/24/2018 5:30:00 PM[/TD]
[TD]2.50
[/TD]
[/TR]
[TR]
[TD]620 Server[/TD]
[TD]10/24/2018 3:53:00 PM[/TD]
[TD]10/24/2018 5:53:00 PM[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]404 Cook[/TD]
[TD]10/24/2018 1:01:20 PM[/TD]
[TD]10/24/2018 5:59:59 PM[/TD]
[TD]4.97[/TD]
[/TR]
</tbody>[/TABLE]


I have another sheet, that is broken down with time stamps as row headers and job title as column headers. For example:

Sales & Labor by hour sheet (Sheet 2)[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Sales[/TD]
[TD]Server hours[/TD]
[TD]Runner hours[/TD]
[TD]Cook hours[/TD]
[TD]Prep Hours[/TD]
[/TR]
[TR]
[TD]1:00 PM
[/TD]
[TD]$5,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2:00 PM[/TD]
[TD]$4,500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3:00 PM[/TD]
[TD]$3,000[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4:00 PM[/TD]
[TD]$2,500[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5:00 PM[/TD]
[TD]$4,200[/TD]
[TD][/TD]
[TD].5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So, I was trying to bring the data from Sheet 1 to Sheet 2 where the total hours would be broken down into the hourly segment rows for all employees that fall within a certain job title/ department. For example for above two sheets, for 512 Runners the hours would be broken down as what is entered above. I need an automated process because I have to do this for a numerous days and numerous restaurants. Any help would be very much appreciated. It has been some time since I regularly used excel and even then I was not the most proficient. Thank you for taking the time to read.

 

[cmschlatt]

First you will need to create a helper column for your Time Card Sheet. Concatenate Clock In hour and Department so you have something like "13505 Prep". You can use the ROUNDDOWN and HOUR formulas to do this:

=ROUNDDOWN(HOUR([Clock In]),1)&[Department]

On your Sales and Labor sheet, change the column headings to match your departments from the first sheet. The do a SUMIF formula to find the hours worked. Assuming your Time Card helper column starts in cell A1 and the data starts in B1 and your sales and labor starts in A1, this formula should work:

=SUMIF('Time Card'!$A:$A,HOUR($A2)&C$1,'Time Card'!$E:$E)

 

[nepsyde]

Hey cmschlatt,

I appreciate you taking the time to write a detailed response and I apologize for responding so late. However, the solution that you proposed is not working for me, maybe I am doing something wrong. Also, does this formula account for the clock out times because I do not see a reference to that column. Meanwhile, I will try to arrange the time formats so that they are consistent between the two sheets to see if that makes a difference. Again, I appreciate your feedback. Thank you.