Change hh:mm to hh.mm

Is there a simple way change teh hour: minute separator in time formatted
cells(columns) to accept the decimal instead of the:? Data entry would be so
much simpler and quicker using the decimal point.
Suggestions appreciated

Thanks

That would really mess things up, don't do it.
If you must do something along those lines see Chip Pearson's page.
Time quick entry in
http://www.cpearson.com/excel/DateTimeEntry.htm

You should also know that time represents a fraction of a day and
you can read about that more on Chip's site, or on mine
http://www.cpearson.com/excel/datetime.htm
and at (there isn't really much overlap)
http://www.mvps.org/dmcritchie/excel/datetime.htm
--
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"JR Hester" wrote in message ...
Is there a simple way change teh hour: minute separator in time formatted
cells(columns) to accept the decimal instead of the:? Data entry would be so
much simpler and quicker using the decimal point.
Suggestions appreciated

Thanks

One way of doing this is to enter the decimal number in one column,
then have another column convert your number to a time. Assuming you
want to enter HH.MM in column C, beginning with cell C2, put this
formula in D2:

=VALUE(INT(C2)&":"&MOD(C2,1)*100&":0))

Format the cell as hh:mm and copy the formula down column D. Now you
can enter your times as hours-point-minutes in column C and these will
appear in Excel time format in column D.

Hope this helps.

Pete


"JR Hester" wrote in message
...

Is there a simple way change teh hour: minute separator in time formatted
cells(columns) to accept the decimal instead of the:? Data entry would be so
much simpler and quicker using the decimal point.
Suggestions appreciated

Thanks

Change it in Windows Regional Language and Settings.

Delete the : and enter a .


Gord Dibben MS Excel MVP

On Fri, 18 Aug 2006 12:50:02 -0700, JR Hester
wrote:

Is there a simple way change teh hour: minute separator in time formatted
cells(columns) to accept the decimal instead of the:? Data entry would be so
much simpler and quicker using the decimal point.
Suggestions appreciated

Thanks

An amendment to my first post.

If you do change the time separator......................

You must always enter time as hh.mm.ss or Excel will interpret as a decimal
number if entered as hh.mm


Gord Dibben MS Excel MVP

On Fri, 18 Aug 2006 12:50:02 -0700, JR Hester
wrote:

Is there a simple way change teh hour: minute separator in time formatted
cells(columns) to accept the decimal instead of the:? Data entry would be so
much simpler and quicker using the decimal point.
Suggestions appreciated

Thanks

Thanks pete, this is the best method proposed thus far. I am going to
implement this my spreadsheet. Thanks again.

"Pete_UK" wrote:

One way of doing this is to enter the decimal number in one column,
then have another column convert your number to a time. Assuming you
want to enter HH.MM in column C, beginning with cell C2, put this
formula in D2:

=VALUE(INT(C2)&":"&MOD(C2,1)*100&":0))

Format the cell as hh:mm and copy the formula down column D. Now you
can enter your times as hours-point-minutes in column C and these will
appear in Excel time format in column D.

Hope this helps.

Pete


"JR Hester" wrote in message
...

Is there a simple way change teh hour: minute separator in time formatted
cells(columns) to accept the decimal instead of the:? Data entry would be so
much simpler and quicker using the decimal point.
Suggestions appreciated

Thanks

Your suggetstion is by far teh most straight forward. It will fit my stated
plan exactly. Thanks also for the second notice; with that tidbit of data, I
think I will stich with Pete's suggested formula approach. Admittedly I was
looking for a setting, which is exactly where you directed.

As with medicinal drugs sometimes the side-effects outwiegh the benefits. I
think that woul dbe the case in this scenario. This second post certainly
saved me quite a bit of frustration, had I went proceeded.

Thansk for sharing your knowledge!

"Gord Dibben" wrote:

An amendment to my first post.

If you do change the time separator......................

You must always enter time as hh.mm.ss or Excel will interpret as a decimal
number if entered as hh.mm


Gord Dibben MS Excel MVP

On Fri, 18 Aug 2006 12:50:02 -0700, JR Hester
wrote:

Is there a simple way change teh hour: minute separator in time formatted
cells(columns) to accept the decimal instead of the:? Data entry would be so
much simpler and quicker using the decimal point.
Suggestions appreciated

Thanks

You're welcome - I hope you spotted the mistake in the formula - it
should be:

=VALUE(INT(C2)&":"&MOD(C2,1)*100&":0")

Pete

JR Hester wrote:
Thanks pete, this is the best method proposed thus far. I am going to
implement this my spreadsheet. Thanks again.

"Pete_UK" wrote:

One way of doing this is to enter the decimal number in one column,
then have another column convert your number to a time. Assuming you
want to enter HH.MM in column C, beginning with cell C2, put this
formula in D2:

=VALUE(INT(C2)&":"&MOD(C2,1)*100&":0))

Format the cell as hh:mm and copy the formula down column D. Now you
can enter your times as hours-point-minutes in column C and these will
appear in Excel time format in column D.

Hope this helps.

Pete


"JR Hester" wrote in message
...

Is there a simple way change teh hour: minute separator in time formatted
cells(columns) to accept the decimal instead of the:? Data entry would be so
much simpler and quicker using the decimal point.
Suggestions appreciated

Thanks