Obtaining the maximum time passing midnight

[russthedutch]

Hello,

I have a spreadsheet which lists operative names and I have to find out each persons time of first task and time of last task. The data is extracted from a warehouse management system which does log each task start and end time.

I have used a min(if statement to look at each persons name and then find the minimum time in the list, but I'm struggling with the end time. the reverse max(if does not work because the operatives spread over midnight and therefore need to take the completed date into consideration (the dates and times are available in the raw data).

I think I need the formula to look at name, for each name display the maximum date found, and then for the name and date look for the maximum time found - but I can't get it to work.

This is the formula I am writing, where Name is in Col. A, last task date is H and finish time is F.

{=MAX(IF($A$2:$A$21=A2,AND($H$2:$H$21=H2,$F$2:$F$21=F2),F2))}

I've also tried index/match to look for the name then compare the last date/time and display the relevant time - but I can't get that to work either.

Can anyone assist please? You'd be responsible for resolving many sleepless nights if you could

Thanks,
Russ

 

[tzw1378]

Why not instead of arbitrarily looking at "Finish Time" just perform a subtraction calculation on the Finish Time minus the Start Time?

The reason I write this is because I assume you can't have a Finish Time without a Start Time? Once you get the hours difference, you can always easily solve for when that specific Finish Time was exactly while also being able to compare and determine who stayed the longest or latest.

 

[russthedutch]

Why not instead of arbitrarily looking at "Finish Time" just perform a subtraction calculation on the Finish Time minus the Start Time?

The reason I write this is because I assume you can't have a Finish Time without a Start Time? Once you get the hours difference, you can always easily solve for when that specific Finish Time was exactly while also being able to compare and determine who stayed the longest or latest.

I do have a start and end time. I don't want to work it out i want to display it.

Big list of names, each with lots of start and end times. I need to show each persons first and last task in a list.

Thanks