Search a source sheet for date, employee email, and a checkbox and return the time

Leslie Merlino

I am trying to build a clock-in/clock-out system. I have a form set up for my source sheet where employee can easily open the form, check the box for clock-in or clock-out and submit the form. From there, I want to pull that data into a time sheet for each employee... I am attempting to find a formula that will pull the time from the time column into the time sheet for that employee on the date and put the clock-in/clock-out times in their respective columns. I am thinking I need a nested IF formula as the source sheet is always growing. I'm having trouble and I'm not sure if it's my formula or that my source sheet is using system columns so I tried to do another column (32) where that information is just entered but it still didn't work. I'm not sure where to go from here. Screenshots of my source sheet and the time card sheet using my own information are attached.

Lidiya Shutaya

@Leslie Merlino

For VLOOKUP, do you have entries that have the same employee AND the same date AND multiple clock ins?

If you don't mind following my thought process real quick - create a helper column that combines the employee, the date, and whether it was a clock in or clock out.

=[Created By]@row + [Clock In]@row + [Clock out]@row + Date@row

If it is a clock in it would look like this: lshutaya@pemnet.comtruefalse07/23/21

If it is a clock out it would look like this: lshutaya@pemnet.comfalsetrue07/23/21

Then in your VLOOKUP you can use these formulas -

Clock In Time for a specific date and employee:

=VLOOKUP([Time sheet column]@row + "true" + "false" + [Date 2]@row, [Concat In]1:Time3, 4, false)

Clock Out Time for a specific date and employee:

=VLOOKUP([Time sheet column]@row + "false" + "true" + [Date 2]@row, [Concat In]1:Time3, 4, false)

When I tested it I can change the date and the clock in/out times will change accordingly. Let me know if this solution fulfills your need, if not, let's keep working through this together!

Best,

Lidiya Shutaya

Lidiya Shutaya

Leslie,

Can you please share the formula you are using for "Time Extract" and the nested IF statement you are using in the time sheet that is giving you the #INVALID OPERATION error?

Thank you!

Leslie Merlino

Sure. Time Extract is just a RIGHT formula from the created column.

I have tried several different formulas. This is the last IF statement I tried....

=IF(AND({Employee Time / Daily Report Created By} = [Employee Name]@row, {Employee Time / Daily Report Date} = Date@row, {Employee Time / Daily Report Clock In} = 1), {Employee Time / Daily Report Times})

I have also tried INDEX/MATCH, but because there are multiple entries that isn't working. I cant do VLOOKUP because there are multiple entries.... I'm at a loss. I'm sure there is a way to do it. I'm just not finding it. Error is Invalid Operation.

I also have some helper columns for the times... Just trying to find a way to make it work.

Time Helper =(VALUE(LEFT([Time Extract]@row, FIND(":", [Time Extract]@row) - 1)) + IF(CONTAINS("P", [Time Extract]@row), IF(VALUE(LEFT([Time Extract]@row, FIND(":", [Time Extract]@row) - 1)) <> 12, 12), IF(VALUE(LEFT([Time Extract]@row, FIND(":", [Time Extract]@row) - 1)) = 12, -12))) + (VALUE(MID([Time Extract]@row, FIND(":", [Time Extract]@row) + 1, 2)) / 60)

Time =INT([Time Helper]@row) + ":" + IF(([Time Helper]@row - INT([Time Helper]@row)) * 60 < 10, "0") + ([Time Helper]@row - INT([Time Helper]@row)) * 60

Lidiya Shutaya

@Leslie Merlino

For VLOOKUP, do you have entries that have the same employee AND the same date AND multiple clock ins?

If you don't mind following my thought process real quick - create a helper column that combines the employee, the date, and whether it was a clock in or clock out.

=[Created By]@row + [Clock In]@row + [Clock out]@row + Date@row

If it is a clock in it would look like this: lshutaya@pemnet.comtruefalse07/23/21

If it is a clock out it would look like this: lshutaya@pemnet.comfalsetrue07/23/21

Then in your VLOOKUP you can use these formulas -

Clock In Time for a specific date and employee:

=VLOOKUP([Time sheet column]@row + "true" + "false" + [Date 2]@row, [Concat In]1:Time3, 4, false)

Clock Out Time for a specific date and employee:

=VLOOKUP([Time sheet column]@row + "false" + "true" + [Date 2]@row, [Concat In]1:Time3, 4, false)

When I tested it I can change the date and the clock in/out times will change accordingly. Let me know if this solution fulfills your need, if not, let's keep working through this together!

Best,

Lidiya Shutaya

Leslie Merlino

Good idea!!!  That thought did not even cross my mind.  I'm not sure yet if they will have multiple in/out times a day.  I hope to find that info out in the next meeting I have with them. I will keep you posted!! 

Leslie Merlino

@Lidiya Shutaya

Good morning!!

Your suggestion worked perfectly, thank you!!!

I did learn that the employees are clocking out for lunches. They require a 30 minute break at minimum. This means that your formula will return the first one it finds, correct?

Lidiya Shutaya

@Leslie Merlino

Glad this is working out for you!

This formula will always display the first match. If you wanted to track breaks you could probably just add another Checkbox to the form and follow the same procedure as above.

Best,

Lidiya Shutaya

Leslie Merlino

That's the thought I was playing with as well. Thank you for your assistance!!