What causes #######

[Gotroots]

What causes ####### to display into infinity. I know normally when this
happens it means the column width is not correct, but what I am getting is
one that auto colmn resizing has no effect on.

The cell causing the problem is E5 it is formatted Custom h:mm with the
formula =D4-C5

D5 and C5 is formatted General and when a value is entered such as 845 it
changes to 8:45:00 AM with a fomat of Custom h:mm:ss AM/PM

I do not want the formatting of D5 or C5 to be altered as it is very
convient to enter 845 to display the time for example.

Anyone got some idea what is happening here.

 

[Jacob Skaria]

If D4 is less than C5 it returns this error.

Select both cells and format to general and compare the values..

Read out the below by Chip Pearson which would give you an idea of how excel
stores date and time
http://www.cpearson.com/excel/datetime.htm

 

[Mike H]

Hi,

You can't enter 845 and have Excel interpret it as a time, Excel will think
it's a number, use properly formatted times.

In E5 which you do have formatted as time the formula is probably producing
a negative time value which will display as ##############. Format as general
to check this and then format all your cells properly and enter delimited
times values such as 8:45

Mike