Time format help needed
 

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
--
Danlinksman

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Dan W." schrieb im Newsbeitrag
...
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
--
Danlinksman

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

-----Original Message-----
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
--
Danlinksman
.

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

--
Mike

"Dan W." wrote in message ...

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

--
Mike

"Dan W." wrote in
message news:55FB6EF0-E601-4E5E-B631-
...
.

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.

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

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

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

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

Bob

"Biff" wrote in message
...
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

-----Original Message-----
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
--
Danlinksman
.

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?

"Ben McBen" wrote:

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.

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

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

Bob

"Biff" wrote in message
...
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

-----Original Message-----
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
--
Danlinksman
.



.

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" wrote in message
...
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

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

Bob

"Biff" wrote in message
...
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

-----Original Message-----
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
--
Danlinksman
.



.

Oh....

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

The COLON IS NOT.

Biff

-----Original Message-----
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" wrote in message
...
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

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

Bob

"Biff" wrote in message
...
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

-----Original Message-----
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
--
Danlinksman
.



.



.

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!

"Biff" wrote:

Oh....

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

The COLON IS NOT.

Biff

-----Original Message-----
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" wrote in message
...
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

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

Bob

"Biff" wrote in message
...
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

-----Original Message-----
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
--
Danlinksman
.



.



.

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

"jopla" wrote:

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?

"Ben McBen" wrote:

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.

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" wrote in message
...
Oh....

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

The COLON IS NOT.

Biff

-----Original Message-----
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" wrote in message
...
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

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

Bob

"Biff" wrote in message
...
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

-----Original Message-----
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
--
Danlinksman
.



.



.

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

LOL!

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

Biff

-----Original Message-----
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" wrote in message
...
Oh....

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

The COLON IS NOT.

Biff

-----Original Message-----
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" wrote in message
...
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

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

Bob

"Biff" wrote in message
...
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

-----Original Message-----
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
--
Danlinksman
.



.



.



.

aah, but you only have to do that once! :-)

Bob

"biff" wrote in message
...
PS setting autocorrect isn't on the numeric keypad either!

LOL!

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

Biff

Time format help needed
 

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
"MikeY" wrote:

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

--
Mike

"Dan W." wrote in message ...

Time format help needed
 

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
--
David Biddulph

"TomS" wrote in message
...
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
"MikeY" wrote:

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

--
Mike

"Dan W." wrote in message
...