Time Input into Excel using decimal point separator

Question

Hi there,

I use excel for timesheet entry and have, on previous windows versions, altered the time input settings in control panel

so I can use a decimal point instead of a colon (ie 8.00 instead of 8:00 to input 8AM).

Windows 7 does not appear to have this customizing feature. Does anyone know if there is such a customization available in windows 7

or if there is a fix for this??

Thanks in advance

Answer 1

The only way I can think of doing this is via Control Panel > Region and Language > Additional Settings > Time and entering HH.mm as the short time format and HH.mm.ss as the long time format.

You can then enter times as 08.15.00 or 08.15.  but not 08.15 since that is taken to be a number.

Making such a global change would be unwise, it seems to me, as it might break other software.

An alternative is just to enter time as e.g. 815 or 1720 and then run a macro to turn these integer values into time values.  See for example:

http://answers.microsoft.com/en-us/office/forum/office_2007-excel/how-do-i-enter-time-into-a-cell-without-having-to/ba6a85ac-036a-e011-8dfc-68b599b31bf5

Answer 2

The only way I can think of doing this is via Control Panel > Region and Language > Additional Settings > Time and entering HH.mm as the short time format and HH.mm.ss as the long time format.

You can then enter times as 08.15.00 or 08.15.  but not 08.15 since that is taken to be a number.

Making such a global change would be unwise, it seems to me, as it might break other software.

An alternative is just to enter time as e.g. 815 or 1720 and then run a macro to turn these integer values into time values.  See for example:

http://answers.microsoft.com/en-us/office/forum/office_2007-excel/how-do-i-enter-time-into-a-cell-without-having-to/ba6a85ac-036a-e011-8dfc-68b599b31bf5

Indeed it helps enter time much faster.

Did you try converting the number to text, substitute de decimal to a colon and formatting the cell to time?

=SUBSTITUTE(TEXT(A1,"00.00"),".",":