#1   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 170
Default 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


  #2   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 903
Default Change hh:mm to hh.mm

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




  #3   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 8,856
Default Change hh:mm to hh.mm

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



  #4   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 22,906
Default Change hh:mm to hh.mm

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


  #5   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 22,906
Default Change hh:mm to hh.mm

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




  #6   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 170
Default Change hh:mm to hh.mm

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




  #7   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 170
Default Change hh:mm to hh.mm

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



  #8   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 8,856
Default Change hh:mm to hh.mm

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





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Want cell ref. to change after sort in other sheet Bullfn33 Excel Discussion (Misc queries) 1 August 6th 06 05:48 PM
Find function alamo Excel Worksheet Functions 1 September 16th 05 02:01 PM
Find function alamo Excel Worksheet Functions 1 September 16th 05 12:47 PM
Macro to change Chart Range when inserting a column Mark Charts and Charting in Excel 1 September 13th 05 01:12 PM
change info in other cells when i change a number in a drop list? macbr549 Excel Discussion (Misc queries) 2 September 11th 05 02:07 AM


All times are GMT +1. The time now is 10:21 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"