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