Format date dd.mm.yyyy to dd/mm/yyyy

[Guest]

Hi,

For exporting purposes to a database, I have to have the dates formatted as
dd/mm/yyyy. Easy you say. Yes, I have formatted the column to be a date and
selected dd/mm/yyyy as the format. Great

Only, it changes the date in any written form EXCEPT where dots are used.
Is there a way I can format by validation or some such thing to error or
replace "." with "/" (i.e dots with slashes.)

Cheers

 

[Frank Kabel]

Hi
sounds like your date values are actually stored as 'Text'- you may try
using 'Data - Text to columns' to convert them to real dates

 

[Guest]

Thanks for that.

That converts text that is already entered. But i want it so that when the
user types 12.01.2004 it automatically converts it to 12/01/2004

Cheers

 

[Frank Kabel]

Hi
if your Excel version does not recognise this date delimiter you'll
have to use a VBA solution.

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(target, Me.Range("A1:A10")) Is Nothing Then
With target
.Value = Replace(.Value, ".", "/")
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Dave Peterson]

If you can live with all dots converting to slashes, you can change the
autocorrect list.

Tools|Autocorrect Options|
replace . (dot)
with / (slash)

This will affect other office programs, too. So when you/the user is done,
delete this entry in the autocorrect list.

(But it's kind of handy when you're doing lots of date entry--and not much
more.)

(off to fix my autocorrect list)

 

[Guest]

No can do, there's figures in the spreadsheet as well (i.e 123.25)