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

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

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

Yes, there is a way to replace the dots with slashes in your date format. You can use the SUBSTITUTE function in Excel to replace the dots with slashes. Here are the steps:

1. Select the column that contains the dates you want to format.
2. Right-click on the column and select "Format Cells".
3. In the "Format Cells" dialog box, select "Custom" from the Category list.
4. In the "Type" field, enter the following format: dd/mm/yyyy;@
5. Click "OK" to close the dialog box and apply the format to the selected column.
6. Now, create a new column next to the column with the dates.
7. In the first cell of the new column, enter the following formula:
   Formula:

   ---

   =SUBSTITUTE(A1,".","/") 


   ---

   (Assuming your date column is A)
8. Press Enter to apply the formula to the cell.
9. Now, drag the formula down to apply it to all the cells in the new column.
10. The new column will now have the dates formatted with slashes instead of dots.

That's it! Your dates are now formatted with slashes instead of dots.

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Kiwi User" <Kiwi schrieb im
Newsbeitrag ...
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

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" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Kiwi User" <Kiwi schrieb im
Newsbeitrag ...
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

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Kiwi User" schrieb im Newsbeitrag
...
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" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Kiwi User" <Kiwi schrieb im
Newsbeitrag
...
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

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)


"Kiwi User" wrote in message
...
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" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Kiwi User" <Kiwi schrieb im
Newsbeitrag ...
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

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)

Kiwi User wrote:

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" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Kiwi User" <Kiwi schrieb im
Newsbeitrag ...
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



--

Dave Peterson

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

"Dave Peterson" wrote:

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)

Kiwi User wrote:

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" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Kiwi User" <Kiwi schrieb im
Newsbeitrag ...
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



--

Dave Peterson