SOLVED

Get the total time between multiple timestamps during the day

Contributor

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. 

Timestamp.png

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.

 

16 Replies

@scaffnull 

=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.

check in check out.JPG

 

@Quadruple_Pawn Thanks I will try! And how will I go with it if I have over 50 names?
alternatively with Excel365 using Quadruple_Pawn's example sheet above:
=MAXIFS($B$2:$B$37,$C$2:$C$27,$F$6:$F$8)-MINIFS($B$2:$B$37,$C$2:$C$27,$F$6:$F$8)
or better if the data is in a formatted table:
=MAXIFS(Table1[Time],Table1[Date],UNIQUE(Table1[Date]))-MINIFS(Table1[Time],Table1[Date],UNIQUE(Table1[Date]))
@mtarler Should the values of the columns be Time for "Time" and Date for "Date", or should they be stored any other way? I have time set at HH:MM:SS
best response confirmed by Grahmfs13 (Microsoft)
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.

It shouldn't matter except how you have the time formatted in the table should be how they are formatted in the output. In other words if you have Time set as #hours like 9.5, 10, 13.5, 17.25 in the original data then the output will be in # hours but if you have it as actual time stamp HH:MM:SS then the output will also be that was so it will show in fraction of a day. So 9:00 - 15:00 is 6 hours and would show as 0.25 in the output unless you format as HH:MM:SS and then it will show 06:00:00
@mtarler thank you! I'm getting a lot of 00:00 so have to see where I have some wrong in the formula.

Or could it be that I have the time as hh:mm:ss, so one row looks like 07:07:27?

Thank you again!
I don't know what your actual data is so I can't say if/when a 00:00 would be correct (e.g. only 1 or 0 entries that day for that name) but I would also make sure if you have 00:00 that you choose HH:MM:SS and not MM:SS. If you don't want to see the 00:00:00 that are due to no data that day then you can either embed the above equation inside an IF( COUNTIFS( ... )>1, [max-min equation], "") or create a custom number formatting with something like: HH:MM; [HH:MM]; ;@ so that excel displays blank instead of 0. (let me know if you need more info/help on either of those)

@mtarler Thank you for your help. Is there a way you would have a similar solution to the photo I attached? 

ScreenDump.png

 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.

@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.

 

@mtarler 

Timestamps.png

 Different formulas than your MAXIFS previous but maybe it helps for a clearer understanding (I'm fairly new to formulas like this)

It appears your 'formulas' are all off by a column, but I figured it out I think. That said you should know about FORMULATEXT() so you don't have to copy and paste or retype the formula you just use that formula where you want to show the corresponding text of the formula and it updates when you update or edit the original formula.
w/r to this sheet, can you attach a copy?
as for total in and total out, did you try the SUMIFS formula I posted above?

@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.

@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

Wow! Really, thank you so much! Great job. Real excel guru