please help, i need to add times greater than 9999:59 on my laptop
running xp on a spreadsheet



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Times in XL are stored as Fractional days (i.e., 03:00 = 0.125). So
you can continue to add times >9999:59, but you can't display them
in time format. You can get a decimal format by multiplying by 24,
e.g.:

A1: 9999:30
A2: 1:45
A3: =(A1 + A2) * 24 ===> 10001.25

Or you could display the colon format as text (though you won't be
able to use it as a number):

A3: =INT((A1+A2)*24) & right(text(mod(A1+A2,1),"h:mm"),3)


In article <(E-Mail Removed)>,
rpt <(E-Mail Removed)> wrote:

> please help, i need to add times greater than 9999:59 on my laptop
> running xp on a spreadsheet

Hi

You can display hours above 24, when you format the cell with custom format
"[h]", or "[h]:mm", etc.

To convert time to text in time format (it's about J.E. McGimpsey's second
formula) also exists a simpler solution
=TEXT(A1+2;"[h]:mm")

--
(When sending e-mail, use address (E-Mail Removed))
Arvi Laanemets


"J.E. McGimpsey" <(E-Mail Removed)> wrote in message
news:jemcgimpsey-(E-Mail Removed)...
> Times in XL are stored as Fractional days (i.e., 03:00 = 0.125). So
> you can continue to add times >9999:59, but you can't display them
> in time format. You can get a decimal format by multiplying by 24,
> e.g.:
>
> A1: 9999:30
> A2: 1:45
> A3: =(A1 + A2) * 24 ===> 10001.25
>
> Or you could display the colon format as text (though you won't be
> able to use it as a number):
>
> A3: =INT((A1+A2)*24) & right(text(mod(A1+A2,1),"h:mm"),3)
>
>
> In article <(E-Mail Removed)>,
> rpt <(E-Mail Removed)> wrote:
>
> > please help, i need to add times greater than 9999:59 on my laptop
> > running xp on a spreadsheet

Thanks for the correction - I brain-dumped that the *display* can go
above 9999:59, even though the parser doesn't recognize *entries*
above 9999:59.

In article <(E-Mail Removed)>,
"Arvi Laanemets" <(E-Mail Removed)> wrote:

> To convert time to text in time format (it's about J.E. McGimpsey's second
> formula) also exists a simpler solution
> =TEXT(A1+2;"[h]:mm")