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

iclancy

New Member
Joined
Mar 12, 2021
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
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"))))))
1628617552923.png

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:

1628619674803.png

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
Joined
Mar 5, 2018
Messages
2,199
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Mar 12, 2021
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Forum statistics

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

Which adblocker are you using?

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
Top