Average of times which are in dd:hh:mm format

**rakshita** · 04-03-2008, 12:06 AM

How can you find the average of times?

I have times like
00:01:00
00:00:15
00:00:08
All are in dd:hh:mm format
I need to find their average which shld be 00:00:28 (approx 27.6 mins)
When I do average(A1:C1) its giving me div/0 error.

Is there any other format i can change my times to?
Thanks!!!

**shg** · 04-03-2008, 12:11 AM

The formula is fine. Make sure the times are actually times, and not stored as text.

**rakshita** · 04-03-2008, 12:18 AM

Yeah all the times are in dd:hh:mm format only. The answers column format is also in dd:hh:mm...Its still givng #Div/0! error..

**rakshita** · 04-03-2008, 12:21 AM

When i am typing 00:01:00 its automatically taking 1/0/1900 12:01:00 AM...Y is that so??

**shg** · 04-03-2008, 12:22 AM

The fact thay you've formatted the cells that way doesn't mean that that's the way they are stored.

Change the format to General and you'll see that the appearance doesn't change, because they are stored as text strings that look like times.

**shg** · 04-03-2008, 12:25 AM

Dates and times in Excel are numbers counting from 1/0/1900 (which is 12/31/1899). The integer part is the number of elapsed whole days, and the decimal part is the time. How it appears is just a matter of formatting.

**rakshita** · 04-03-2008, 12:26 AM

yeah thts correct... But now how do I change this...What do I change that it takes it as dd:hh:mm and calculates the average...Sorry but am quite new to all this

**shg** · 04-03-2008, 12:35 AM

Format the cells in whatever date format you wish (e.g., dd:hh:mm), enter the data, and use the average formula.

The salient point is this: dates and times are just numbers to Excel, like any other. They can be formatted to look different, but the underlying number is the same. What you have are strings of text ("Bob", "Hello", "01:32:33")that happen to look like dates, but are still just strings to Excel. It happens sometimes when data is imported.

If you set the format to a date format, then you can convert those strings to dates by retyping them (ouch!), or

Select an empty cell and copy it
Select all the cells containing those date strings
Do Edit > Paste Special, check Values and Add, and then click OK

The reason this works is that when Excel is told to perform an arithmetic operation with a string (adding zero in this case), it coerces the numeric string to a number (in this case, a date) and then performs the operation.

"Bob" and "Joe" couldn't be converted to numbers, but a string that looks like a date can be (and is) converted to a date so the operation can be performed.

Capische?