Excel Formula to Find First and Last Login Times by Agent and Date
Question details
The user needs to retrieve the earliest login time and the latest logout time for specific agents on particular dates, dealing with multiple login sessions per day without adding time values together.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Building a dashboard from a large raw report to track daily attendance, shift start, and shift end times for individual agents.
- Observed behavior
- Requires a formulaic method to extract specific minimum and maximum time values based on multiple conditions (agent name and date) from a continuous log of sessions.
Ensure your date and time data are formatted correctly as Excel Date and Time values rather than text strings. Excel calculates time as decimal fractions of a day, which allows functions like MIN and MAX to evaluate chronological order perfectly.
Use the GROUPBY Function (For Microsoft 365 Users)
A modern, dynamic array formula that instantly groups raw data by agent and date, outputting the earliest and latest times automatically.
If you are using Excel 365, the new GROUPBY function can generate a complete summary table in one go. You can pair it with HSTACK to request both the MIN (first login) and MAX (last logout) simultaneously.
Click on the top-left cell where you want your new summary table to begin generating.
Type the formula =DROP(GROUPBY(A2:B18,C2:C18,HSTACK(MIN,MAX),0,0),1) adjusting the ranges. Let A2:B18 be your Agent and Date columns, and C2:C18 be your Time column.
Select the resulting time columns, press Ctrl+1, navigate to Number > Time, and select your preferred time format (e.g., h:mm AM/PM).
Use MINIFS and MAXIFS Functions (For All Modern Versions)
Extract the minimum (first) and maximum (last) times based on specific agent and date criteria using standard conditional formulas.
Manage Timesheets Efficiently with WPS Spreadsheet
WPS Spreadsheet fully supports advanced conditional formulas like MINIFS and MAXIFS, making it effortless to track employee login times, build dashboards, and organize massive datasets without lag.
- 1. Open your report: Launch WPS Spreadsheet and open your attendance or login log file.
- 2. Create a summary sheet: Add a new worksheet and list your unique agents and dates.
- 3. Apply MINIFS/MAXIFS: Use the MINIFS function for the earliest login and MAXIFS for the latest logout, just as you would in Microsoft Excel.
- 4. Format and save: Format the results as Time and save your perfectly functioning dashboard.
Frequently Asked Questions
Why is my formula returning a decimal number instead of a time?
Spreadsheet software stores dates as serial numbers and times as fractional decimal values (e.g., 0.5 is 12:00 PM). If your formula returns a decimal, the calculation is correct; you simply need to change the cell format to 'Time'.
How do I handle agents who forgot to log out, resulting in a 0?
If an agent has no logout record matching the criteria, MAXIFS returns 0. You can wrap your formula in an IF statement to catch this: =IF(MAXIFS(...)>0, MAXIFS(...), "No Logout").
Can I use an array formula for older Excel versions without MINIFS?
Yes. If you are using Excel 2016 or earlier, you can use the classic array formula: =MIN(IF((Agent_Range=Agent)*(Date_Range=Date), Time_Range)). Remember to press Ctrl+Shift+Enter to evaluate it as an array.
Does WPS Office support these time-tracking formulas?
Absolutely. WPS Spreadsheet is highly compatible with Excel formulas and fully supports MINIFS, MAXIFS, and array logic, making it a great free alternative for time tracking and data analysis.
