Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 10th 04, 06:03 AM
Kiwi User
 
Posts: n/a
Default 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

  #3   Report Post  
Old December 10th 04, 07:09 AM
Kiwi User
 
Posts: n/a
Default

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



  #4   Report Post  
Old December 10th 04, 07:43 AM
Frank Kabel
 
Posts: n/a
Default

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
news
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




  #5   Report Post  
Old December 10th 04, 09:48 AM
Bob Phillips
 
Posts: n/a
Default

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
news
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







  #6   Report Post  
Old December 10th 04, 10:26 PM
Dave Peterson
 
Posts: n/a
Default

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
  #7   Report Post  
Old December 12th 04, 11:41 PM
Kiwi User
 
Posts: n/a
Default

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
prevent 4/5 in a cell automatically transfer to a date format kei Excel Discussion (Misc queries) 3 December 9th 04 11:52 AM
Problem with Date format from VBA code twig Excel Discussion (Misc queries) 3 December 7th 04 06:01 PM
Date on two lines using a custom cell format possible? .:mmac:. Excel Discussion (Misc queries) 5 December 4th 04 09:41 PM
How can I format a cell so date field only displays the Month? tk_2u Excel Discussion (Misc queries) 3 December 4th 04 12:54 AM
How can I defeat Excel's auto-reformating into date format? stebro Excel Discussion (Misc queries) 2 December 2nd 04 03:39 PM


All times are GMT +1. The time now is 05:56 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017