Login/Logout Times Before/After Midnight

matthewlrx

New Member
Joined
Jul 4, 2022
Messages
16
Office Version
  1. 2021
Platform
  1. Windows
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:

Excel Formula:
{=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:

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

Here is what my data looks like:

1.png


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.

2.png


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

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
3,769
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,175,957
Messages
5,900,534
Members
434,835
Latest member
cmenconi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top