Time has come today



Normal
0


false
false
false







MicrosoftInternetExplorer4










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


=”5678:00″+”5876:00″


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.

    Cheers,
    Kirill

  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 *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>