Average of Times - Divided by Zero Error

[Jim LeMond Jr]

Can anyone help me format my times so that they average in a Pivot Table?

I receive a report of login/logout times in a weird Unix blend format. I have to convert it to xx:xx AM/PM.

When I put together a Pivot Table, the average of the times produces a "Divided by Zero" error.

Thank you!!

Jim

 

[Skovgaard]

Hi Jim,

Perhaps something with your formats...
I'm not able to re-create your problem, see below...

/Skovgaard

 

[Alex Blakenburg]

The left alignment of your times is a strong indicator that your times are actually text values.
Having the format set to Time as in your image will only affect the format of anything being stored as numbers. It does not change the data type if Excel is recognising it as Text.
How have you "converted it to xx:xx AM/PM ? Have you by any change used the string functions to do that or the formatting function "=Text(cell,format)"

To prove it is Text simply format it to a different format then the one you are using and if doesn't change it is text.