Forum Discussion
Solved
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 pe...
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.
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]))
=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
- 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