Jul 25 2022 06:23 AM - edited Jul 25 2022 06:24 AM
Hi,
I'm trying to find a way to get the hours between the first login (timestamp) that the employee did and the last when the person left the office.
If you see the attached picture, the person has a lot of stamps everyday when he/she goes in or out of the office. But I want to get the calculated value from the first time he/she comes in, to that he/she stamps out.
I've been trying to create a filter with some MIN/MAX for the earliest time and the latest time, but can't seem to figure it out.
Is there anyone with an easy solution so I can display it for everyday that person is coming in/out of the office.
Thanks for any possible help.
Jul 25 2022 06:38 AM
=INDEX($B$2:$B$37,MATCH(1,($C$2:$C$37=$F6)*($D$2:$D$37=$G6)*($A$2:$A$37=H$5),0))
You can try this formula for the data layout of the attached example. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.
Jul 25 2022 06:46 AM
Jul 25 2022 06:50 AM
Jul 25 2022 07:00 AM
Jul 25 2022 07:02 AM
Solution@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.
Jul 25 2022 07:07 AM
Jul 25 2022 07:32 AM
Jul 25 2022 09:12 AM
Aug 01 2022 01:19 AM
@mtarler Thank you for your help. Is there a way you would have a similar solution to the photo I attached?
I'm trying to calculate the total time the person has checked in for that day, and also get a value for total time outside. So "Gate 1 In" means the person checks in, "Gate 1 Out" means the person checks out.
Thank you for any kind of help.
Aug 01 2022 06:16 AM
@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.
Aug 01 2022 06:34 AM
@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.
Aug 01 2022 06:49 AM
Different formulas than your MAXIFS previous but maybe it helps for a clearer understanding (I'm fairly new to formulas like this)
Aug 01 2022 09:25 AM
Aug 01 2022 10:46 PM
@mtarler Yes you are correct! Thanks for being patient. I will attach a copy. But how would I get the total time out? Can only seem to get the total time in.
Thanks again.
Aug 02 2022 09:30 AM
@scaffnull So I just piled them all into 1 equation. It isn't pretty but may do your needs:
=CHOOSE(MIN(5,COUNTIFS(Table3[[#Headers],[Name]]:[@Name],[@Name],Table3[[#Headers],[Date]]:[@Date],[@Date])),
[@Name] & " : " & TEXT( [@Date],"yyyy-mm-dd"),
"Total time In: " & TEXT(SUMIFS([Time],[Name],[@Name],[Date],[@Date],[Gate],"*Out")-SUMIFS([Time],[Name],[@Name],[Date],[@Date],[Gate],"*In"),"h\h m\m"),
"Total time out: " & TEXT(MAXIFS([Time],[Name],[@Name],[Date],[@Date],[Gate],"*Out")-MINIFS([Time],[Name],[@Name],[Date],[@Date],[Gate],"*In")-SUMIFS([Time],[Name],[@Name],[Date],[@Date],[Gate],"*Out")+SUMIFS([Time],[Name],[@Name],[Date],[@Date],[Gate],"*In"),"h\h m\m"),
"Total: " & TEXT(MAXIFS([Time],[Name],[@Name],[Date],[@Date],[Gate],"*Out")-MINIFS([Time],[Name],[@Name],[Date],[@Date],[Gate],"*In"),"h\h m\m"),
"")
see attached
Aug 02 2022 09:35 PM
Jul 25 2022 07:02 AM
Solution@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.