Averaging and adding/subtracting time values before and after 12:00AM

[iclancy]

Hi,

I'm trying to solve two separate issues having to do with averaging time values that include values both before and after 12:00 AM. The first involves this formula and the following cells:

=+IF(DM37=""," ",IF(DN37=""," ",IF(DN37<DM37,"Error",IF(DM37<DO37,"Early Arrival",IF(DM37=DO37,"Ontime",IF(DM37<DO37+TIME(0,5,0),"Late Arrival","5+ Minutes Late"))))))

DM and DN are under "Time In" and "Time Out" columns, respectively, and the row we're working with is 37.

In this case, Excel stops at IF(DN37<DM37,"Error" in the above formula, because it reads 12:03 AM as an earlier time (in General number format, cell DM = 0.997... and cell DN = 0.002...).

Similarly, I am using averaging formulas which include these cells and others:
=+IFERROR(AVERAGEIF($B$4:$EX$4,FL$4,B37:EX37),"")
and
=+IFERROR(AVERAGEIF($B$4:$EX$4,FM$4,B37:EX37),""),

where FL4 and FM4 refer to "Time In" and "Time Out," and cells between B4 and EX4 are similar Time In/Time Out values representing different days of the month.

The above formula results in this:

again the row is 37. cell FN is the difference of the two columns =IFERROR(FM37-FL37,""), which here results in a negative number (General format -0.144...) and cannot be displayed.

I'm expecting averaged values here in the ballpark of 12:00 am, but since there are several values in the sheet which are either earlier or later than 12:00 AM, the resulting values are wildly different from what I need.

The time values are copy-pasted into the sheet by another party and sent to me.

So, my question is: Is there an easy way to either automatically convert entered time values or tweak the formulas I listed to convert the time values into a format that would work in the above calculations?

Please let me know if I need to clarify any of the above further or provide more information. Thanks!

 

[Toadstool]

Hi IClancy,

The challenge is obviously that having the date included would solve the problem. If you can assume time differences would never be more than a day then I'd use another sheet and populate with the time but add one day if it had obviously clocked over into another day (and you'd need to consider the DO column also).

Something like this:

IClancy.xlsx
	DM	DN	DO
36	Time In	Time Out
37	11:57:00 PM	12:03:00 AM	11:35:00 PM
38	11:57:00 PM	12:03:00 AM	12:05:00 AM
39	12:03:00 AM	12:05:00 AM	11:57:00 PM
Data

IClancy.xlsx
	DM	DN
37	0.997917	1.002083
38	0.997917	1.002083
39	0.002083	0.003472
Work

Cell Formulas
Range	Formula
DM37:DM39	DM37	=Data!DM37
DN37:DN39	DN37	=IF(Data!DN37<Data!DM37,Data!DN37+1,Data!DN37)

I must admit I'm not following how the average of the times helps you?

 

[iclancy]

Thanks, that's what I ended up doing -- adding another sheet that adds +1 to the times if they are between 12:00 AM and 4:00 AM, and using those new times for calculations on the main sheet.