enter time in simple text and convert to hours/minutes

Y

YanYan

I have a long list of hourly schedules to enter for 30+ employees.
Is there a way to enter just a number, for instance "9" into a cell and have
it automaticaly convert to 9:00 instead of me having to type the : and
minutes every time? I want to be able to type in 8, 9, 10 and have it convert
to 8:00, 9:00, 10:00 etc.
 
Y

YanYan

Thank you, is there anything that I can do without having to add a helper
column. I dont want to add anymore to the sheet.
 
R

Ragdyer

You can do it with code:

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

OR, you could use a little trick with 'AutoCorrect'.

<Tools> <AutoCorrect>
In the"Replace" box, enter 2 periods (decimal points) [ .. ],
In the "With" box, enter a colon ( : ), then <OK>.

NOW, when it becomes necessary to enter times, simply enter your hour
numbers followed with 2 periods ( 9.. ), which will give you 9:00 AM,
Or 12..45, which will yield 12:45 PM.
 
Y

YanYan

Thank you, great options.

Ragdyer said:
You can do it with code:

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

OR, you could use a little trick with 'AutoCorrect'.

<Tools> <AutoCorrect>
In the"Replace" box, enter 2 periods (decimal points) [ .. ],
In the "With" box, enter a colon ( : ), then <OK>.

NOW, when it becomes necessary to enter times, simply enter your hour
numbers followed with 2 periods ( 9.. ), which will give you 9:00 AM,
Or 12..45, which will yield 12:45 PM.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
YanYan said:
Thank you, is there anything that I can do without having to add a helper
column. I dont want to add anymore to the sheet.
Click to expand...
Click to expand...
 
R

Ragdyer

Thank you for the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
YanYan said:
Thank you, great options.

Ragdyer said:
You can do it with code:

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

OR, you could use a little trick with 'AutoCorrect'.

<Tools> <AutoCorrect>
In the"Replace" box, enter 2 periods (decimal points) [ .. ],
In the "With" box, enter a colon ( : ), then <OK>.

NOW, when it becomes necessary to enter times, simply enter your hour
numbers followed with 2 periods ( 9.. ), which will give you 9:00 AM,
Or 12..45, which will yield 12:45 PM.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
YanYan said:
Thank you, is there anything that I can do without having to add a
helper
column. I dont want to add anymore to the sheet.

:

Yan Yan

You could use a helper column and enter the formula

=(L2/24)/60

Where L2 is where you're entering data (8,5,7,8, etc.)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
[email protected]


I have a long list of hourly schedules to enter for 30+ employees.
Is there a way to enter just a number, for instance "9" into a cell
and
have
it automaticaly convert to 9:00 instead of me having to type the :
and
minutes every time? I want to be able to type in 8, 9, 10 and have
it
convert
to 8:00, 9:00, 10:00 etc.
Click to expand...
Click to expand...
Click to expand...
 
Top