SUBSTITUTE question replacing "." with ":"

Hi Guys,

Doing up a spreadsheet and am trying to use =SUBSTITUTE in the cell but of course if you type something else it over rides the formula.

What I would like to do is, some people record time as decimal eg: .5 = 30 minutes but I want to force the use of 00:00 and not 00.00

Whats the best way to force the : and/or change the entry.

So for example if someone enters 1.5 that will change to 1:30

Is it possible with a formula, conditional formatting or a VBA

Chip Pearson gives some VBA solutions on his site here:

http://www.cpearson.com/Excel/DateTimeEntry.htm

These allow for rapid date and time entry without needing to use any separators.

Hope this helps.

Pete