Forum Discussion
Get the total time between multiple timestamps during the day
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.
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)
mtarler Thank you for your help. Is there a way you would have a similar solution to the photo I attached?
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.