Login/Logout Times Before/After Midnight

[matthewlrx]

Hi Everybody

So I have agents who work shifts and I am trying to find a formula which will find the earliest login time and the latest login time. Sounds easy right? Wrong... I have agents (Agent4 as example) who work overtime and go past midnight so their "Logoff" date is the next date but the formula i use sees this as their current login time. If the agents dont work past midnight this formula works great, but if they work after midnight things dont work well.

Here is the formula I am using for Login:

{=IF(COUNTIF(HoursData!H:H,B3)=0,"Absent",MIN(IF(Table2[Agent Name (First Last)]=B3,Table2[Online Time])))}

Here is the formula I am using for Logout:

{=IF(R2="Absent","",MAX(IF(Table2[Agent Name (First Last)]=B2,Table2[Offline Time])))}

Here is what my data looks like:

Here is what my current formulas pick up. Agent 1 and 2 are fine, Agent 3 and 4 have issues.

Agent3's logout time should be 0:08
Agent4's Login time should be 15:00. The Login 0:15 is when they did overtime at midnight previous shift. Their Logout time should also then be 0:00:07. This person logged in and out twice in their shift due to system issues.

Does anybody have a idea what i can do to get this working?

 

[Micron]

Do the math or comparisons on the complete date/time value. Example of time span calculation: 07/14/2022 00:08:09 AM - 07/13/2022 11:23:05 PM
Date time values are floating point double data type.

These values are not 100% identical due to the couple of seconds between generating each, but should show the general idea:
?now
7/14/22 11:19:12 AM

?cdbl(now)
44756.4716782407

If you ignore/remove the time portion of a date, then today would be 44756 and there's no way to get time spans between 2 such numbers or to compare times without dates.