Time has come today




I saw a question in the newsgroups today where a guy was asking
how he could get more than 10,000 hours in a cell, adding a hour number larger
that 10,000 to another time number the
formula did not give a correct answer. 

Some of the answers suggested that Excel cannot hold a value
of 10,000 or more hours. This is actually incorrect. It is true that it is only
possible to enter a single value of 9,999:59:59 into a cell, but it can be
tricked into holding more.

For example, enter 5000:00 in cell A1 and the same in B1,
and a formula of =A1+B1 in another cell will show 10000:00.

It is even possible to enter it in a cell using a formula
such as


and we get an answer of 11554:00.

Of course, the cell does have to be entered as [h]:mm to
see the result as more than 24 hours.


5 thoughts on “Time has come today”

  1. Hi Bob,
    I can’t check it right now, but, if I am not mistaken, the limitation is only for time format entry, so you can actually trick it by introducing time serial or, instead of using strings, use something like =11554/24 into a cell formatted with [h]:mm

  2. Hi Kirill,

    You cannot use the Time function, because that is  working on a 24 hour basis, So Time(25,0,0) will show up as 1:00 even if formatted as Cool:mm.

    But you can put in say 419 and formatted as Cool:mm will show greater than 10,000 hours.

    But I think the point is that people like to input time in a time format, and I was trying to show that it is possible.


    BTW the cool dude happy face is a open  square bracket – the letter h – close square bracket

  3. Hi Bob,
    I totally agree with you :-) Btw, by “time serial” I meant what you illustrated with 419, not the TIME() function.


  4. I don’t think it is anything I did Jimmy, apart from typing h in square brackets. As you can see Kirill’s attempt went the same way.

    I cannot see any option on the blog to turn it off. But it does make you smile which can’t be bad.

Leave a Reply

Your email address will not be published. Required fields are marked *