Login-Logout details

[Rhon Alvarez]

Hi Team,

Asking for your help with regards to the attached file wherein the corresponding Time In and Time out of each employee would reflect for the provided date based on the raw data from another sheet.

I tried using helper columns but i managed to do only the Time In portion.

kindly provide formula for the Time Out portion and should there be a much simpler formula than what i've done for the Time in portion, please do provide/edit as well.

Thank you in advance.

 

[Peter Bartholomew]

@Rhon Alvarez
There are a number of problematic features about your file. The first is 'why is it an xls file?'; what version of Excel are you using? The next problem is that none of your times are numbers; they are all text strings which makes concepts like 'minimum' and 'maximum' difficult.

To convert the times I used Replace All to put a space before AM/PM which allowed Excel to recognise times. I then converted both your timesheet and summary to Excel tables to see what I was doing.

= MINIFS(Timesheet[Login Time],
  Timesheet[Agent Name], [@[Employee Name]],
  Timesheet[Login Date], Date )
 
= MAXIFS(Timesheet[Logout Time],
  Timesheet[Agent Name], [@[Employee Name]],
  Timesheet[Login Date], Date )

I didn't use the Logout date because I didn't believe it to be correct.