Time format help needed

[Guest]

I am trying to type in 12:34 as 1234(for example) and have the format convert
it back to 12:34 Is there a way of formatting this conversion? I will need it
to be a time format since it is a time card key entry.

Thanks

 

[Frank Kabel]

Hi
you need vBA for this. See:
http://www.cpearson.com/excel/DateTimeEntry.htm

 

[Biff]

Hi!

An alternative to the VBA solution is to *TEMPORARILY* set
AutoCorrect to replace a period "." with the colon ":" and
enter the "times" like 12.34. You would have enter the
times in a 24 hr clock format but can reformat them after
they've all been entered.

This is lots faster and easier than using the 1 2 shift
colon 3 4 space P M method.

Just remember to reset AutoCorrect after you're done.

Biff

 

[MikeY]

Format the concerned cells as 0":"00
123 --> 1:23
1234 --> 12:34

However, please note that 5 or more digits number would show as below;

1234567 --> 12345:67

 

[Biff]

Hi!

Be advised that this method only changes the appearance of
the numbers and does not convert them to true times.

Using this method the cell looks like 12:34 but the actual
underlying value is 1234 (one thousand two hundred thirty-
four)

Biff

-----Original Message-----
Format the concerned cells as 0":"00
123 --> 1:23
1234 --> 12:34

However, please note that 5 or more digits number would show as below;

1234567 --> 12345:67

message (e-mail address removed)...

 

[Ben McBen]

Another alternative is to use an adjacent cell to do the
conversion for you (this may not be acceptable). You
can use a formula thus:

=TIMEVALUE(LEFT(D5,2)&":"&RIGHT(D5,2))


where D5 contains your number.

 

[Ben McBen]

Another alternative is to use an adjacent cell to do the
conversion for you (this may not be acceptable). You
can use a formula thus:

=TIMEVALUE(LEFT(D5,2)&":"&RIGHT(D5,2))


where D5 contains your number.


Sorry - I initially posted this as a subthread to a
previous reply

 

[Ben McBen]

Saw this on another thread - what a nice, clever person
that Pearson is:

http://www.cpearson.com/excel/DateTimeEntry.htm

 

[Bob Phillips]

but is it any easier than the 24 hour clock with colon? One shift hardly
does it for me.

Bob

 

[Guest]

Can you guys help me with a problem? On an anniversary date, I want to add
vacation days to an individual's vacation accrued cell. How can I do that?

 

[Biff]

but is it any easier than the 24 hour clock with colon?

One shift hardly does it for me.

Ah, but that one shift requires two hands!

Payroll clerks/timekeepers that either don't want to use
the VBA method or don't know about it will really
appreciate the difference.

Biff

 

[Bob Phillips]

You may know more payroll clerks than me, but I doubt it, it still requires
coming off of the number keypad that those type of users will use more than
perhaps you or I. And if they don't know about Chip's solution, educate
them!

Bob

 

[Biff]

Oh....

The decimal point IS ON the numeric keypad....

The COLON IS NOT.

Biff

 

[Guest]

Thank you. I have no problem with military conversion when using hours,
minutes and 10 key.... however the other dept. that are keying want to put
time as traditional 24 hour clock with traditional 60 minute /minutes... I
like your suggestions. Thanks!

 

[Guest]

I have a space limitation I am working with and have no additional colum
room, and the data needs to be visable for our clerical input specialist...
Thanks for the suggestion. I will log for future considerations as it may
help in future versions.

Thanks

 

[Bob Phillips]

I wasn't advocating the colon method, just stating that with having to
*TEMPORARILY* set the Autocorrect to replace colon with dot, and still
having to enter that character, it's advantage over the colon method is
slight, and Chip's solution leaves it trailing in it's wake

Bob

PS setting autocorrect isn't on the numeric keypad either!

 

[biff]

PS setting autocorrect isn't on the numeric keypad either!

LOL!

....and you won't find an object module there either! <g>

Biff

 

[Bob Phillips]

aah, but you only have to do that once!

Bob

LOL!

...and you won't find an object module there either! <g>

Biff

 

[TomS]

If you have more than 4 digits, perhaps you wish to show seconds too, simply
use

00":"00":"00

Now 123456 will show as 12:34:56

You can continue this format for as many digits as you need, just keep
repeating the ":"00

 

[David Biddulph]

I'm not quite sure why this thread has been revived, having been dormant
since December 2004, but remember the warning given then, that formatting in
that way will *NOT* give a number that allows time calculations to be
carried out.

For example, if you double your 12:34:56 you would get 24:69:12, instead of
25:09:52