enter time in simple text and convert to hours/minutes

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.

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]HIS


"YanYan" <[email protected]> wrote in message
news:[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.

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.

"Nick Hodge" wrote:

> 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]HIS
>
>
> "YanYan" <[email protected]> wrote in message
> news:[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.
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
> "Nick Hodge" wrote:
>
>> 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]HIS
>>
>>
>> "YanYan" <[email protected]> wrote in message
>> news:[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.
>>
>>
>>

Thank you, great options.

"Ragdyer" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
> > "Nick Hodge" wrote:
> >
> >> 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]HIS
> >>
> >>
> >> "YanYan" <[email protected]> wrote in message
> >> news:[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.
> >>
> >>
> >>
>
>

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"YanYan" <[email protected]> wrote in message
news:[email protected]...
> Thank you, great options.
>
> "Ragdyer" wrote:
>
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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.
>> >
>> > "Nick Hodge" wrote:
>> >
>> >> 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]HIS
>> >>
>> >>
>> >> "YanYan" <[email protected]> wrote in message
>> >> news:[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.
>> >>
>> >>
>> >>
>>
>>