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

#### iclancy

##### New Member
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!

### Excel Facts

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

#### Toadstool

##### Well-known Member
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
DMDNDO
36Time InTime Out
3711:57:00 PM12:03:00 AM11:35:00 PM
3811:57:00 PM12:03:00 AM12:05:00 AM
3912:03:00 AM12:05:00 AM11:57:00 PM
Data

Cell Formulas
RangeFormula
DM37:DM39DM37=Data!DM37
DN37:DN39DN37=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

##### New Member
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.

Replies
4
Views
449
Replies
1
Views
244
Replies
9
Views
282
Replies
2
Views
265
Replies
4
Views
232

Threads
1,148,256
Messages
5,745,698
Members
423,969
Latest member
CHHeights

### 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

### 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