Forum Discussion

scaffnull's avatar
scaffnull
Brass Contributor
Jul 25, 2022
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...
excel
Formulas and Functions
  • mtarler's avatar
    mtarler
    Jul 25, 2022

    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.

OliverScheurich's avatar
OliverScheurich
Gold Contributor
Jul 25, 2022

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.

 

mtarler's avatar
mtarler
Silver Contributor
Jul 25, 2022
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]))
  • scaffnull's avatar
    scaffnull
    Brass Contributor
    Jul 25, 2022
    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
    • mtarler's avatar
      mtarler
      Silver Contributor
      Jul 25, 2022
      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

Resources