limit on [h]:mm time format?

[Guest]

Hi All,

[h]:mm time format seems to work only up to 9999 hours, XL2003 doesn't
handle 10000 hours as time, 10000:00 remains text. Is this an unchangeable
limit?

Thanks,
Stefi

 

[JE McGimpsey]

From XL Help ("Specifications"):

Largest amount of time that can be entered 9999:99:99

Calculated times can be larger (at least 66600000:00:00.

 

[Guest]

See

http://groups.google.com/group/micr...2c4f4f4e809?lnk=st&q=&rnum=1#0d0922c4f4f4e809


The maximum time value that you can type into a cell is 9999:59:59. If you
type a value that is greater than or equal to 10,000 hours (10000:00:00), the
time appears as a text string.


The maximum time value that you can calculate using a formula is
71003183:59:59. This value equates to 12/31/9999 23:59:59, which is the
maximum
time that you can use, at least in Excel 2002.

Excel 2003 the same?

From Ron Rosenfeld

 

[Guest]

[h]:mm is a number format. It does not have the limit you specify. Put 500
in a cell and format it as [h]:mm. What does have a limit is an entry like

10000:00

then as you say, it is not interpreted as a time value. This isn't
changeable.

 

[Guest]

Thanks Toppers, I've made a workaround with UDFs, but it's really a stupid
limit! I came across such large numbers of hours in reporting flight hours of
airplanes, which is quite a normal task, there is nothing exceptional in it!

Regards,
Stefi


„Toppers†ezt írta:

Stefi,
I tried the following with cells formatted as [h]:mm

I entered 9999:00 in row 1 and then copied down: all cells looked OK and a
SUM also looked OK. If I overtyped a value > 9999 then I had your result i.e.
time now appeared as text!

A bug ???

I don't have an answer!

Hi All,

[h]:mm time format seems to work only up to 9999 hours, XL2003 doesn't
handle 10000 hours as time, 10000:00 remains text. Is this an unchangeable
limit?

Thanks,
Stefi

 

[Guest]

Stefi,
I tried the following with cells formatted as [h]:mm

I entered 9999:00 in row 1 and then copied down: all cells looked OK and a
SUM also looked OK. If I overtyped a value > 9999 then I had your result i.e.
time now appeared as text!

A bug ???

I don't have an answer!

 

[Guest]

you don't need a UDF. Just enter your hours as

=120000/24

format the cell as [h]:mm

--
regards,
Tom Ogilvy

Thanks Toppers, I've made a workaround with UDFs, but it's really a stupid
limit! I came across such large numbers of hours in reporting flight hours of
airplanes, which is quite a normal task, there is nothing exceptional in it!

Regards,
Stefi


„Toppers†ezt írta:

Stefi,
I tried the following with cells formatted as [h]:mm

I entered 9999:00 in row 1 and then copied down: all cells looked OK and a
SUM also looked OK. If I overtyped a value > 9999 then I had your result i.e.
time now appeared as text!

A bug ???

I don't have an answer!

Hi All,

[h]:mm time format seems to work only up to 9999 hours, XL2003 doesn't
handle 10000 hours as time, 10000:00 remains text. Is this an unchangeable
limit?

Thanks,
Stefi

 

[Guest]

You can do it yourself. time is simply a number which counts seconds and
given in days

You can convert days to minute by multiplying by (24 * 60). then convert
this number to hours and minutes.

Use this formula
=CONCATENATE(TEXT(INT((F5*24*60)/60),"#"), ":",TEXT(MOD((F5*24*60),60),"#"))

[h]:mm is a number format. It does not have the limit you specify. Put 500
in a cell and format it as [h]:mm. What does have a limit is an entry like

10000:00

then as you say, it is not interpreted as a time value. This isn't
changeable.

--
Regards,
Tom Ogilvy

Hi All,

[h]:mm time format seems to work only up to 9999 hours, XL2003 doesn't
handle 10000 hours as time, 10000:00 remains text. Is this an unchangeable
limit?

Thanks,
Stefi

 

[Sandy Mann]

[h]:mm is a number format. It does not have the limit you specify.

The display limit in XL97 seems to be 71003183:59

Which is of no interest to anyone other than the de Havilland Tiger Moth
that I learned to fly in <g>

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

[h]:mm is a number format. It does not have the limit you specify. Put
500
in a cell and format it as [h]:mm. What does have a limit is an entry
like

10000:00

then as you say, it is not interpreted as a time value. This isn't
changeable.

--
Regards,
Tom Ogilvy

Hi All,

[h]:mm time format seems to work only up to 9999 hours, XL2003 doesn't
handle 10000 hours as time, 10000:00 remains text. Is this an
unchangeable
limit?

Thanks,
Stefi

 

[David Biddulph]

As reported elsewhere, that is 31st December 9999 23:59.
--
David Biddulph

[h]:mm is a number format. It does not have the limit you specify.

The display limit in XL97 seems to be 71003183:59

Which is of no interest to anyone other than the de Havilland Tiger Moth
that I learned to fly in <g>

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

[h]:mm is a number format. It does not have the limit you specify. Put
500
in a cell and format it as [h]:mm. What does have a limit is an entry
like

10000:00

then as you say, it is not interpreted as a time value. This isn't
changeable.

--
Regards,
Tom Ogilvy

Hi All,

[h]:mm time format seems to work only up to 9999 hours, XL2003 doesn't
handle 10000 hours as time, 10000:00 remains text. Is this an
unchangeable
limit?

Thanks,
Stefi

 

[Guest]

Thanks to all of you for your posts, they fully cleared the question for me!
Nevertheless I still think that this is a stupid limit!

Regards,
Stefi


„Stefi†ezt írta:

 

[SteveW]

XL2000 doesn't have this limit
not on inputted or calculated values

Steve

Thanks to all of you for your posts, they fully cleared the question for
me!
Nevertheless I still think that this is a stupid limit!

Regards,
Stefi


„Stefi†ezt írta:

Hi All,

[h]:mm time format seems to work only up to 9999 hours, XL2003 doesn't
handle 10000 hours as time, 10000:00 remains text. Is this an
unchangeable
limit?

Thanks,
Stefi

 

[Guest]

Hi Steve,

How did you find this information? I tried also in XL2000, and I found, that
entering 10000:00 results in a text instead if time value just like in XL2003!

Regards,
Stefi


„SteveW†ezt írta:

XL2000 doesn't have this limit
not on inputted or calculated values

Steve

Thanks to all of you for your posts, they fully cleared the question for
me!
Nevertheless I still think that this is a stupid limit!

Regards,
Stefi


„Stefi†ezt írta:

Hi All,

[h]:mm time format seems to work only up to 9999 hours, XL2003 doesn't
handle 10000 hours as time, 10000:00 remains text. Is this an
unchangeable
limit?

Thanks,
Stefi

 

[SteveW]

I *always* try out the stuff before I post.
Didn't save the test book though, and when I tried it again

I can't either, but if you type 9999:00 into a cell and drag it
down one it will put 10000:00 into the cell
displaying as 19/02/1901 16:00:00 if you click on the formula bar

Sorry for the confusion, I did think I had typed it in rather than
calculated it.
But I did get it into a cell without using a formula

Steve

Hi Steve,

How did you find this information? I tried also in XL2000, and I found,
that
entering 10000:00 results in a text instead if time value just like in
XL2003!

Regards,
Stefi


„SteveW†ezt írta:

XL2000 doesn't have this limit
not on inputted or calculated values

Steve

Thanks to all of you for your posts, they fully cleared the question for
me!
Nevertheless I still think that this is a stupid limit!

Regards,
Stefi


„Stefi†ezt írta:

Hi All,

[h]:mm time format seems to work only up to 9999 hours, XL2003 doesn't
handle 10000 hours as time, 10000:00 remains text. Is this an
unchangeable
limit?

Thanks,
Stefi

 

[Guest]

Thanks, Steve. I tested XL2003 and it did exactly what you wrote. It
confirmed my opinion that this is a very stupid and unnecessary limitation!

Regards,
Stefi


„SteveW†ezt írta:

I *always* try out the stuff before I post.
Didn't save the test book though, and when I tried it again

I can't either, but if you type 9999:00 into a cell and drag it
down one it will put 10000:00 into the cell
displaying as 19/02/1901 16:00:00 if you click on the formula bar

Sorry for the confusion, I did think I had typed it in rather than
calculated it.
But I did get it into a cell without using a formula

Steve

Hi Steve,

How did you find this information? I tried also in XL2000, and I found,
that
entering 10000:00 results in a text instead if time value just like in
XL2003!

Regards,
Stefi


„SteveW†ezt írta:

XL2000 doesn't have this limit
not on inputted or calculated values

Steve


On Thu, 22 Feb 2007 08:19:13 -0000, Stefi

Thanks to all of you for your posts, they fully cleared the question
for
me!
Nevertheless I still think that this is a stupid limit!

Regards,
Stefi


„Stefi†ezt írta:

Hi All,

[h]:mm time format seems to work only up to 9999 hours, XL2003
doesn't
handle 10000 hours as time, 10000:00 remains text. Is this an
unchangeable
limit?

Thanks,
Stefi