Forum Discussion
Get the total time between multiple timestamps during the day
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.
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.
16 Replies
=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.
- 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])) - OliverScheurich Thanks I will try! And how will I go with it if I have over 50 names?
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.
