Forum Discussion
Get the total time between multiple timestamps during the day
scaffnull Just saw your question about additional names. Here is a table for days and names:
=MAXIFS(Table1[Time],Table1[Date],F3#,Table1[Name],G2#)-MINIFS(Table1[Time],Table1[Date],F3#,Table1[Name],G2#)Were Table1 is were the data is, F3# is a formula =UNIQUE(Table1[Date]) with all the unique dates and the G2# is a formula with all the unique names.
See attached.
scaffnull so I have a number of questions that need to be considered but a 'simple' answer is that you might want a formula like:
=SUMIFS(Table1[Time],Table1[Date],date,Table1[Name],name,Table1[Gate],"Gate 1 out")-
SUMIFS(Table1[Time],Table1[Date],date,Table1[Name],name,Table1[Gate],"Gate 1 in")so basically you sum all the time stamps for that date and name going out minus the time stamps going in.
Then you have all the questions:
how to determine the name and date and location of those formulas. So will that list always be formatted that way? does the output have to be centered like that? Will you add the formulas and such manually? For the time "out" is that 24hours - time in or ONLY the time out in BETWEEN the in times? Is there a chance they will clock "out" after midnight?
So I could see creating a formula that could ASSUMING that particular sorting/list order it would automatically list the requested outputs in the first few lines of the grouping (i.e. IF the date/name changed 1,2,3... lines above then output ... formula). BUT I think a separate table like the solution given above would be cleaner.
mtarler Thanks again for an answer.
To answer your questions -
The data extracted just comes as text, so I have just formatted a table. And on the right hand side of the table is just notes I made (doesn't have to be like that). I will add data that comes in, and just update the formula to take the updated rows.
For the time "out" is 24 hours, so total time from first check in to last time check out for that day. There won't be a scenario where they check out after midnight.
The only issues that could happen is if the check in / out gate doesn't work and they have two "check in/out" after each other. But that I can sort out and remove one row, because those times it typically diff 4-5 seconds.