Results 1 to 5 of 5
Thread: Time & Formula Usage

20101210, 10:36 #1
 Join Date
 Jun 2010
 Location
 Quincy, MA
 Posts
 62
 Thanks
 2
 Thanked 0 Times in 0 Posts
I find I don't very well understand the use of time in conjunction with the AVERAGE formula and with conditional formatting. With a delivery deadline of 2:00 AM, I use a conditional format to alert me if I exceed the deadline, while I also track the average delivery time.
2:40 AM12:10 AM12:13 AM12:08 AM11:59 PM11:59 PM12:15 AM
The average on this data turns out to be 10:46 AM which is really not the case. Also, if I say the value for Conditional Formatting is > 2:00, the two 23:59 values pop out in RED; to avoid that false positive if I choose the value > 1/1/1900 2:00, I have to remember to input anything actually greater than 2:00 AM as 1/1/1900 X:XX or in the example, 1/1/1900 2:40. Is there a more efficient way of configuring the Conditional Formatting while at the same time getting a better average? I'm using Excel 2003 (11.8328.8329) SP3. Thanks

20101210, 12:09 #2
 Join Date
 Jan 2004
 Location
 Praha
 Posts
 1,052
 Thanks
 69
 Thanked 118 Times in 103 Posts
Let's tackle the average issue first.
The average of the times you quote is 12:34PM, which is what Excel gives me using the AVERAGE function when I use the 24 hour clock system. I have attached the file: the format of the cells in question is "Time" so that you can see that the times are correctly entered.
However if you make all the times "AM" then the average is 10:34 which is similar to what you are experiencing  you cannot be entering the morning and afternoon times correctly? Can you post a sample spreadsheet showing how you are entering the data ?

20101210, 13:56 #3
 Join Date
 Jun 2010
 Location
 Quincy, MA
 Posts
 62
 Thanks
 2
 Thanked 0 Times in 0 Posts
Thank you for your response. One of the things I did notice is that your times reflected PM when all times should be AM (except for the two at 11:59 / 23:59). I have included a sample file as you requested, please note that Columns A & C have two different Conditional Formatting Rules. A = 2:00 while C = 1/1/1900 2:00. The Delivery time is 2:00 AM or 2:00 not 2:00 PM or 14:00. Again, thank you for your response. [attachment=90880:WOPR.xls]

20101210, 18:46 #4
 Join Date
 Jan 2004
 Location
 Praha
 Posts
 1,052
 Thanks
 69
 Thanked 118 Times in 103 Posts
I do find a 12 hour clock prone to confusion (mine) and always use a 24 hour clock for calculations.
Anyway, the reason your columns A and C give a different average is that in column C the first time (02:40) is entered in the cell as a Date/Time (1 January 1900, 02:40). You cannot get a meaningful average from mixed dates and times.
As for the conditional formatting, I am not clear what it is that you want. Please can you give a clear description of the conditions when you want the cell to be highlighted in red ? When you say "> 2:00" did you mean "later than 02:00" (later than 2AM) ? 23:59 is indeed later than 02:00 which is why those cells are red. If you meant something else, please say what.
Thanks.

20101212, 09:05 #5
 Join Date
 Dec 2009
 Location
 London, UK
 Posts
 4
 Thanks
 0
 Thanked 0 Times in 0 Posts
Excel stores dates and times as numbers representing days from 00:00 on Jan 1 1900. Therefore, (to 5 dp)
12:00 AM = 0.00000
12:00 PM = 0.50000
11:59 PM = 0.99931
02:40 AM = 0.11111
12:10 AM = 0.00694
etc.
The average of the equivalent values of those times, according to my calculations, is 0.30595, which equates to 7:20 AM, which is what Excel comes back with too. MartinM, I'm not sure where you got 12:34 PM from, but you appear to be right about Pete converting them all to AM (although I get 10:46 AM which is what Pete got).
Pete, I think where you're running into trouble is (1) AM/PM needs to be explicitly specified in the time, and (2) times in Excel are treated as if they are on the same day, i.e. 12:00 AM  11:59 PM so 11:59 PM is always later than 2:00 AM. If you want to process times on different days you will have to specify the date too.
What I would suggest in your case, however, is convert those times into the differences from 2:00 AM which, from your post, I understand is your target time. That way you can calculate averages without tracking dates too.
You will need to convert the times into numbers since Excel cannot format negative numbers as times or dates, and then treat 11:59 PM as a negative number, telling Excel to treat it as the previous day by subtracting 1 (to get 0.00069 to 5dp). Subtract those values from 2:00 AM (0.08333) and you have the difference in days.
You should then be able to calculate the average from those values, and convert that back to a time by adding 2:00 AM and applying an appropriate number format. Remember to add 1 if you get a negative number!