Stop Excel changing cell format?

I have a spreadsheet collecting measurements plus a date and time stamp. For ease of data entry some users want to enter a time string as "h.mm" with a dot rather than the more usual colon "h:hh".

I have a validation macro that scans the cell value and replaces any '.' with ':'.

Please Login or Register  to view this content.

This works, and the macro completes OK.

BUT - as the replace is done Excel changes the format of the cell from 'General' to Custom "h:mm". This means that the next time a value is entered it is converted behind the scenes to a date/time value. e.g "9.35" is converted to a number representing "09 01 1900 8:24:00 AM" which messes up all sorts of things.

Is there any way to stop Excel from changing the cell format?

And for bonus points : Can anyone point me to some code that validates and reformats entered time strings? I'm sure I'm not the first person to want to extract something like "21:35" from variants of 21.35, 9.35pm, 21:35PM etc..

Thanks!

I think you are better to force theusers to enter teh time in the set format instead of trying to convert an incorrect format after the event.

"better to force theusers to enter teh time in the set format"

You are probably correct, but this is a specific request to allow them to enter times using a '.'

It's simpler to do with one finger using only the numeric pad while standing up and holding stuff in your other hand. Real world problem...

Anyone else able to help?

An easy method would be to have two cells; the entry cell and result time cell .

This converts the EnterTime cell value and put the result in the cell to the right

Please Login or Register  to view this content.

Thanks for the reply. Your solution would work, as would having separate cells for hour and minute so they just press enter to move from one to the other.

What I have done for now is just reset the cell format as soon as Excel 'fixes' it...

Please Login or Register  to view this content.