Finding Average Time of Day in 2003

[aty2626]

Hi, I work for a company that gets survey results from our customers. I am looking at all the data that is exported to excel in '03 and in the row where the time of the surveys show, I can not for the life of me figure a way to find the average. The format is mm/dd/yyyy hh:mm:ss am/pm - I want to take out the mm/dd/yyyy and have tried formatting it to time in hh:mm but when i do this, it averages out a weird number, i believe because it is recognized the : in between hh:mm as a ratio.

Does anyone know how to correct this? I am trying to do this to find trending into when my workers are being surveyed. To be able to find the average time of day.

THanks!!!

 

[mvptomlinson]

Dates in Excel are whole numbers signifying the number of days since January 1, 1900. Times are decimal numbers between 0 and .9999 that represent a fraction of a day.

Today, Feb 14, 2012, is serial date 40953. Therefore, 40953.5 would equal today + a half a day, or Noon today. 40953.75 would equal today + three-quarters of a day, or 6:00pm today.

So to extract the time value from a date-time value, all you need to do is get rid of the integer portion of the value, e.g.

=A1-INT(A1)

Format that cell as hh:mm:ss (or just hh:mm if you don't need to be accurate to the second).

When you average a range of these cells, you'll likely get a decimal number. Format that cell as hh:mm as well, and you should be all set.

 

[T. Valko]

Hi, I work for a company that gets survey results from our customers. I am looking at all the data that is exported to excel in '03 and in the row where the time of the surveys show, I can not for the life of me figure a way to find the average. The format is mm/dd/yyyy hh:mm:ss am/pm - I want to take out the mm/dd/yyyy and have tried formatting it to time in hh:mm but when i do this, it averages out a weird number, i believe because it is recognized the : in between hh:mm as a ratio.

Does anyone know how to correct this? I am trying to do this to find trending into when my workers are being surveyed. To be able to find the average time of day.

THanks!!!

Try something like this...

Book1
	A	B
2	4/11/2011 2:38 AM	8:29 AM
3	5/4/2011 3:35 AM	_
4	3/20/2011 4:58 PM	_
5	4/8/2011 10:47 AM	_
Sheet1

This array formula** entered in B2:

=AVERAGE(--TEXT(A2:A5-INT(A2:A5),"h:mm"))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Format as Time