Base 60 input

[Howard]

Using Excel 2003.

How can I simply format cells to display times using digits entered with
punctualtion in base 60 format - I think its called sexagesimal.

In other words, if I type the digits "1234" the cell displays "12:34" i.e.
the time 12:34 p.m., without having to type a colon.

Thanks

Howard

 

[Fred Smith]

Use a custom format of: 0":"00

Regards
Fred

 

[David Biddulph]

But of course that won't allow you to do calculations as times. To do that
you'd need to convert, perhaps with a formula such as =--TEXT(A1,"00\:00")
and formatting as time.

 

[Howard]

Thanks for that Fred, but it sems to be just a mask that doesn't convert to
base 60 i.e. time.

For example, I input 1234 in A1 (displays 12:34) , 1528 in B1 (displays
15:28). But =B1-A1 outputs 2:79. What I am aiming for is to output 02:54
(i.e. 2h 54m).

Is there a cell format for that?

Used this, but I'm not

 

[Roger Govier]

Hi

Take a look at Chip Pearson's site
http://www.cpearson.com/Excel/DateTimeEntry.htm

--
Regards
Roger Govier

Thanks for that Fred, but it sems to be just a mask that doesn't convert
to base 60 i.e. time.

For example, I input 1234 in A1 (displays 12:34) , 1528 in B1 (displays
15:28). But =B1-A1 outputs 2:79. What I am aiming for is to output 02:54
(i.e. 2h 54m).

Is there a cell format for that?

Used this, but I'm not




__________ Information from ESET Smart Security, version of virus
signature database 4777 (20100116) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4777 (20100116) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Howard]

I had a look at Pearson's site. It's a great resource, but is pitched above
my level of understanding. I don't know where to start entering VB code
(though I'd like to).

Is there a simple "open this and paste the code there" first step?

H

 

[מיכ×ל (מיקי) ×בידן]

To the best of my knowledge - if you need TO DO(!) calculations on those
fields - Freds advice is not good enough for you
In such a case - take a look at:
http://www.cpearson.com/excel/DateTimeEntry.htm
OR:
use an helper-column, formatted as hh:mm, and try on of the formulas:
=(TRUNC(A1/100,0)+RIGHT(A1,2)/60)/24
OR:
=(REPLACE(REPT(0,4-LEN(A1))&A1,3,0,":"))*1
*** in cell A1 type the 1234
Micky

 

[Howard]

Shalom Micky.

The helper column idea has its limitations, but as I think it coul dbe some
time before I can follow the VB thing, I think it will do what I need for
now.

Thanks

 

[Fred Smith]

No it doesn't because you specifically asked only to format the cell. If you
want to convert the number to a time, use:
=TIME(INT(A1/100),MOD(A1,100),0)
Format as a time.

Regards,
Fred