Hi all

This has been giving me a headache for a long time and i hope you can help me out.

I have a shared document where a date is written in coloumn A.
Most people write the date with a dot. (european time) and it is not possible to change what they are used to be doing. SO

ex. 05.12 (5th of December)
I need this to be shown as:
05-12 (as this format is the proper way with the regional settings of the computers, and conditional formatting only works with this)

I've tried custom format of the cell (dd-mm)
but when you then type in 05.12 it will keep come out as 12-05. and this is very frustrating.

It has to be swapped on the fly so ive tried many codes ive found on different sites
one example would be:

Private Sub Worksheet_Change(ByVal Target As Range)
'updateby Extendoffice
    On Error Resume Next
    Application.EnableEvents = False
    If Not (Intersect(Target, Me.Range("A8:A200")) Is Nothing) Then
        Target.Value = Replace(Target.Value, ".", "-")
    End If
    Application.EnableEvents = True
End With
End Sub
---
But this does some wierd stuff everytime you click the cell

Anyone have a golden solution for this? Written in a code i can just copy paste

I need excel not to think i am from UK just because i type date with "." and autoswap "." to "-"
Find and replace seems to work, just cant get it to work instantly when typed.


Im sure there is an easy way to do this, just not for me :D!