# 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. KL says:

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. bobphillips says:

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 :mm.

But you can put in say 419 and formatted as :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. KL says:

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. JP says:

Not sure what you’re doing, but your typing is being converted to smilies. It’s pretty funny, actually.

5. bobphillips says:

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.