Time Format, change the separator

J

John

Is it possible to format a cell that usually displays as
[hh]:mm so that it can be entered using a "." as a
sparator instead of ":" Basically to make time entry
quicker and easier.
 
J

Jacques Brun

John
yes it is possible to format a cell un such way that a time is displayed as 11.23 rather than 11:23 but it will not solve your problem which is to ease data entry.

The reason is that the format is used to display data and not to convert data entered. the custom format "[hh].mm" will display a cell containing 8:30 (8H30) as 8.30. However if you enter "6.5" in this cell it will be interpreted as the decimal number 6.5 meaning 6.5 days or 156 hours and the cell will be displayed as "156.00"

One solution to solve your problem could be to write an event-triggered routine that would convert the number entered into the internal value of the corresponding time.

Another solution is to enter the dot form of the time in one column an to convert it to the internal time representation in a different column with a formula like

=Int(A1)/24+100*(A1-Int(A1))/1440

Basically this formula converts the entry in days : The hours before the dot are divided by 24 and the minutes after the dot are divided by 1440 because there are 24 x 60 = 1440 minutes in a day

For the former solution (event triggered routine) the principle of the calculation is the same but it is to be included as visual basic code in the worksheet_Change routine associated to the sheet where the data entry is done. Although it is not very complex to develop it will probably take more time than entering some ":" rathen than ".". Such a development would only be justified if you have to enter repeatedly thousands of time data.

Regard
Jacques Bru

----- John wrote: ----

Is it possible to format a cell that usually displays as
[hh]:mm so that it can be entered using a "." as a
sparator instead of ":" Basically to make time entry
quicker and easier
 
R

Ragdyer

The girls at my plant use this every Monday morning when they are entering
time card data into XL for payroll:

<Tools> <AutoCorrect>,
In Replace enter the colon :)),
In With enter the period (.),
Then <OK>.

They then use their 10-key abilities, and enter the times as decimals, where
XL does the appropriate substitution.
After the time cards are done, they just revert everything back to normal.

They love this procedure, as compared to the old way of doing the payroll.
 
Top