How can I write a macro when I don't know beforehand where to put my formulas?

Hello. I work for a company that requires employees to use ID badges in order to enter or leave the property. Whenever someone uses their badge, the data is sent to a spreadsheet that displays the person's name, employee ID number, the gate they used and whether they were entering or exiting (in the same cell; for example: Gate 2-In), and what time they used their badge. A new sheet starts with the start of a new day. We need to create a macro that will calculate the time an employee has spent on the property. I know how to subtract the time out from the time in in order to find the total time, but the tricky part comes when you consider the night shift; some employees will be shown checking out, without having checked in (that data being on the sheet from the day before) or vice versa, and that number changes from day to day. How can I write something that will disregard the employees who are shown as only leaving or entering, and will automatically find and do the calculations for the in-out pairs? The only way that I know how to write macros is with each formula being assigned to a specific cell, which clearly in this case will not work. I know there has to be a way, but unfortunately I have no knowledge of VBA.

Additional details: The names are ordered alphabetically, and if the same names appears, they are ordered from earliest to latest. So supposedly, entries will all appear in pairs, time in/time out, with the exception of arriving or departing night shift employees.

Any suggestions are appreciated! I'm clueless about what to do. Thank you!

Welcome to the forum JAMeyer.

There is definitely a way to do this. Someone else may be able to assist you without, but for me to write the correct code, posting a workbook would be really helpful. Can you do this?

Steve

Hello JAMeyer,

I have attached a workbook containing a macro that calculates the time worked for employees who checked in and out on the same day. It also makes sure that the T-TYPE = "Valid". I added a column that concatenates the employee names as well.

Let me know if this helps.

Steve

Always glad to help out where I can!