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
0
User (141)
12/10/2004 6:03:02 AM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
1231 Views

Similar Articles

[PageSpeed] 40

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 User@discussions.microsoft.com> schrieb im
Newsbeitrag news:85E35702-DF86-4A48-AD40-72F410EC8B09@microsoft.com...
> 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

0
frank.kabel (11126)
12/10/2004 6:09:30 AM
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 User@discussions.microsoft.com> schrieb im
> Newsbeitrag news:85E35702-DF86-4A48-AD40-72F410EC8B09@microsoft.com...
> > 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
> 
> 
0
KiwiUser (1)
12/10/2004 7:09:01 AM
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" <KiwiUser@discussions.microsoft.com> schrieb im Newsbeitrag
news:D5CD8F4A-E11A-4FD6-8CB3-E43AA47E490F@microsoft.com...
> 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 User@discussions.microsoft.com> schrieb im
> > Newsbeitrag
news:85E35702-DF86-4A48-AD40-72F410EC8B09@microsoft.com...
> > > 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
> >
> >

0
frank.kabel (11126)
12/10/2004 7:43:32 AM
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" <KiwiUser@discussions.microsoft.com> wrote in message
news:D5CD8F4A-E11A-4FD6-8CB3-E43AA47E490F@microsoft.com...
> 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 User@discussions.microsoft.com> schrieb im
> > Newsbeitrag news:85E35702-DF86-4A48-AD40-72F410EC8B09@microsoft.com...
> > > 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
> >
> >


0
bob.phillips1 (6510)
12/10/2004 9:48:04 AM
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 User@discussions.microsoft.com> schrieb im
> > Newsbeitrag news:85E35702-DF86-4A48-AD40-72F410EC8B09@microsoft.com...
> > > 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
0
ec357201 (5290)
12/10/2004 10:26:30 PM
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 User@discussions.microsoft.com> schrieb im
> > > Newsbeitrag news:85E35702-DF86-4A48-AD40-72F410EC8B09@microsoft.com...
> > > > 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
> 
0
User (141)
12/12/2004 11:41:02 PM
Reply:

Similar Artilces:

Number formatting in chart
The secondary axis in my chart is dollar value. It is showing in thousands, which is what I want, but the data labels I would like it to show 177 only, for example, instead of 177,883. It would be understood that the labels are in thousands. Is this possible? Connie Okay, I guess I didn't make myself very clear. I will try to take a different approach. How do I customize the format of numbers in a column so that it rounds 177,883 to 178? Connie "Connie Martin" wrote: > The secondary axis in my chart is dollar value. It is showing in thousands, > which is ...

Something similiar to conditional formatting
Having data in few columns I want to do something similair to conditional formatting. Let's say that in column A I have data concerning people's names. I want to change data in columns located far away from column A, e.g in column Z (There I will insert people's addresses). So the best way to see column A is to freeze part of table in the way that i could see the first column. Is it possible to make cells change (exactly: change the text in the cell, by bolding it or inserting background) when they will "feel" cursor in the other in the same row? Summurizing: w...

Charts In 2002 Maintaining Formats
How do I maintain the formating of a dual axis chart in Excel 2002 when the data is updated? I have tried to set the chart type as the default and this work as long as I create a new chart. On the old chart when I update the data the chart immediately updates to a single axis stacked bar chart Help. Doug - Is it a pivot chart? These are notorious for losing their formatting after an update. If you used a particular chart type when constructing the chart, you should be able to reapply the same chart type. Right click on the chart (not just on a series), choose Chart Type from the pop up...

change the year in a list of dates ex 1/3/2003 to 1/3/2004
I have a list of dates, all in the year 2003 1/3/2003, and 5/17/2003, I want to change just the year to 1/3/2004 and 5/15/2004 in the whole list. How do I do that? say the dates you have now are in col A in a new column, type =a1+365 and copy down then do a /copy/special/values on the new column so it becomes static and no longer refers to col A... then delete col A and move teh new column to Col a work on a copy of your file to make sure I have it right... "Elaine" <Elaine@discussions.microsoft.com> wrote in message news:57252856-7A95-4C46-8B96-10E4D127EAB9@microsof...

How to 'convert' photos saved in .doc format?
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel My first post here - please be patient. I'm helping a friend who has just converted from her PC to a shiny new iMac and I've struck a problem that I don't know how to resolve. On her PC she saved many (hundreds) of photos in .doc format (don't ask)! She thought that was how to do it. She's adamant that she needs to keep all these images. <br><br>I've never faced the problem before and don't seem to be able to find any way of converting the .doc formatted pictures to e....

My document won't open, tells me file format is not valid
I have tried to open an excel document IN Excel and it isn't opening. I get an error message that says the file format is not valid. What happened? I did open the file ONCE with Word. Could I have screwed it up by doing that? I don't know how to get the file open in excel. HELP! >>Could I have screwed it up by doing that? Oh yes. :-( Better start looking for a backup. Excel 2002/3 each have repair facilities in them but i wouldn't hold out much hope in this instance - Worth a try if you have access to them though - Just hit File / Open and then click on the dropdown...

Auto increment date in header
Hello, I want to create a document and in header to put the date like: Thursday, 31.12.2009 and on second page, in header to appear Friday, 01.01.2010 and on third page Saturday, 02.01.2010 and so on Have anybody an idea how to do it? Thank you! Hi Lucrix, To see how to do this and just about everything else you might want to do with dates in Word, check out my Word Date Calculation Tutorial, at: http://lounge.windowssecrets.com/index.php?showtopic=249902 or http://www.gmayor.com/downloads.htm#Third_party In particular, look at the item titled 'Calculate a Date Se...

New History Item, Mail Message sends all messages as Rich Text Format
We are using Business Contact Manager 2007 with Outlook 2007. We are not using Exchange server as a backend, just plain old pop3/smtp mail servers. Problem we are having is when we open up an account, Click on New History Item, Mail Message, and if we send an attachment with that, the person that receives the message gets it in Rich Text format. If we right click on the Email address itself and click on Send Mail. The message will be sent out fine. Right click on the message and looking at the properties of the email address has the internet format listed as "Let Outlook decide the ...

Formatting Pages
I would like to print the data on a sheet in the landscape view, but the page numbering in the top right corner with the number rotated like the portrait view. This is so I can bind my data tables in a book with the numbers in the top right corners like a normal book. Thank you. Carolyn Permission granted <g> View>Header and Footer>Custom header>Upper right>*click the # icon a space, then the ### icon. This will cause Page n of n Pages to appear on the preview and hard copy. -- Greeting from the Gulf Coast! http://myweb.cableone.net/twodays "Carolyn" <cda...

Outlook & IMAP format
Will Outlook in the forseeable future support this format for E mail. Outlook Express supports this but Outlook doesn't and it is the superior product ...

Converting this "French" currency format to "English"?
I found this neat coding in the custom number format box: _ * #,##0.00_) $_ ;_ * (#,##0.00) $_ ;_ * "-"??_) $_ ;_ @_ It produces this when there is no dollar amount: - $ I like this format, but of course, need to write the currency in the more standard English format since this particular sheet is in English and not French so that I should get this instead: $ - Dunceville me ... I tried everything I could think of but I can't get it to come out right. Every attempt gave me an error code with the kind suggestion to use the "built-in number formats" :oD :oP ...

Formatting excel chart in powerpoint -- size scales are blank
At times when i copy an excel chart into powerpoint and want to resize it, the size scales are blank (height/width). I click on reset which shows me 100%, but if i need to go back and resize it again it goes back to being blank. It happens sometimes. Have Office XP, and tried everything i can possibly think of -- paste special, linking, and nothing works. Hope someone has encountered this same problem and has a solution if there is one. THANKS For re-sizing the best option is to copy as a picture: 1. select the area of the worksheet you want to copy 2. while holding down the SHIFT key, p...

Date problem #7
i am trying to do a matrix for some training we have to keep records o and I got all my columns and names , but I want to use a second shee to log what date this happens. I would like to use OK on sht 1 an enter date on sht 2 Does any one have any Ideas to make this work office 2000 -- Message posted from http://www.ExcelForum.com ...

Pivot Table - Grouped Dates
Hi All, I have a grouped pivot by date. Works fine except for the format of the date doesn't allow sorting in the way that I need. The data has the dates in the format: 4/17/2006 14:21 After grouping in the pivot on that field by day (to remove the time portion) I get: 10-Apr Sorting the pivot gives: 10-Apr 10-Mar 11-Apr 12-Apr 13-Apr 13-Mar 14-Apr 14-Mar 15-Apr 15-Mar 16-Mar 17-Apr Rather than by year/month/day. I tried changing the field format in the pivot and it doesn't do anything at all to how the format in the pivot looks? I need the pivot data to sort as actual day...

Enter date into Formula
I want to have a date without using date function in another function, i.e., MATCH(01/02/2010,OFFSET(CC1stDate,1,0):OFFSET(CCDateCol,CCRowLast-1,0),1)+5 I use the #01/02/2010# in VBA. Is there a simular notation in EXCEL? -- Thanks, Don On Sat, 16 Jan 2010 19:40:01 -0800, DRA <DRA@discussions.microsoft.com> wrote: >I want to have a date without using date function in another function, i.e., >MATCH(01/02/2010,OFFSET(CC1stDate,1,0):OFFSET(CCDateCol,CCRowLast-1,0),1)+5 > >I use the #01/02/2010# in VBA. Is there a simular notation in EXCEL? The DATE function ...

Entering a date that then gets changed to next weekday Thursday's date, say?
I don't want to make the current sheet I'm working on too complicated, so prefer not to go the macro route. Was hoping a formula would take care of this (?). If I enter a date via "^;", it would be so nice if the sheet knew to change the date to the following Thursday's date. i.e., when I type ^; into the currently empty date cell, it puts today's date of "2006.12.27.Wed" which I then stop to fix to nearest Thursday. Instead, it would be very helpful if a formula or something non-macro did that for me and changed it to the nearest Thursday's date, in...

Conditional formatting #131
Hi I’m working with a spreadsheet control, and I realize that lot’s of the vbscript programming is like excel programming. Well spreadsheet control does not support conditional formatting, so to go around this problem I tried a couple of codes but I had no look. What I’m trying to do is change the font color of a cell if the result of a formula is less then 0. thanks. Manually select cell A1 and pull-down Format > Conditional Formatting and select: Cell is less than 0 and apply your format. The Macro Recodred can capture this: Sub Macro1() Range("A1").Select...

Filling in a cell based on another cells date criteria
I want to populate a cell with the value of another cell..based on the following date criteria - My date cell is stored in (F5) and the cell I want to populate (D20) has a value stored in (C20) If the first of the month is a Mon-FRI then the cell (D20) gets populated on the date in the week which the first of the month falls on...example: if 8/1/08 were a wed the field (D20) would get populated with the value in cell (C20) UNLESS If the first of the month were a Sat..like 11/1/08... if the first of the month falls on a saturday on a sat...then the field (D20) would get populated with (C20&#...

Rounding up numbers in hh:mm:ss format
I have a columnn of numbers stored in the hh:mm:ss (custom) format. I need to round up all cells that have ss>0 an add 1 to mm; in other words all cells with seconds should be rounded up to the next minute. How can I do that? Cheers, Thorbjorn Hi try =ROUNDUP(A1*24*60,0)/(24*60) -- Regards Frank Kabel Frankfurt, Germany Thorbjorn Sundboe wrote: > I have a columnn of numbers stored in the hh:mm:ss (custom) format. I > need to round up all cells that have ss>0 an add 1 to mm; in other > words all cells with seconds should be rounded up to the next minute. > How can I do ...

Excel XP saving to Excel 2K format.
I am trying to save a modified excel sheet as a Office 2K version (that is what the client is using) but I get a "Document Not Saved" error with no help available. Does anyone have a clue as to how to save it as an older version? ";-\)" <james.holland@comcast.net> wrote: >I am trying to save a modified excel sheet as a Office 2K version (that is >what the client is using) but I get a "Document Not Saved" error with no >help available. > >Does anyone have a clue as to how to save it as an older version? > Excel XP and 2000, as well as ...

Expiry date of the current version ?
I have been using Microsoft money for a since 2000. I am on version 2004/2005. I want to buy the latest version. I am not sure whether to buy now or wait for the next version ? if i buy the current version will i be able to download the transaction automatically ? I am surprised that a reputed company like Microsoft has no mention about this on their website. I would appreciate if you guys advice me on this. The newest version is Money Plus Deluxe and you can go to www.microsoft.com/money and you will find it there. "srn" wrote: > I have been using Microsoft money fo...

xls file format is not valid
Have an XP PC trying to open an Excel 97 file that is stored on a Windows 2000 server named survey.xls and getting an error that the .xls:file format is not valid. We are using the same program that created it. How do we open it. Thanks for your help!!!! Try to copy your file and rename it making sure that the extension is .xls An extra period or space could make all the difference. Otherwise, you file could have been corrupted. Not a happy thought Let me know if this helped you Jack I'd try to open a copy of the file from a local harddisk, too. But sometimes excel workbooks g...

Add st and th to dates
Hi All, Is there a way in excel to format a date so that it automatically includes the "th" and "st". I.e. so 1/8/05 --> 1st August 2005? Ta Andi Hello- Excel provides no such date formatting, and trying to create a custom format like that would be impossible due to the variable 'st', 'rd', 'th', etc. & when each should be used. Perhaps it can be accomplished with VBA, but unless someone has it available & is willing to share, it would probably be more trouble to write than its worth... Unless you need to use the dates for cal...

Dynamically Copying Changing Conditional Formatting
Is it possible to dynamically copy the formatting of a 1st cell whose format changes to another 2nd cell. Not just on creation, but anytime the original cell changes formatting either conditionally or manually? (Excel 2003, XP) Thanks! -- sdm ...

formating macro
Hi, I have problem in macro codes. I have recorded macro for rows formatting. For recording a macro I have chosen line no.22. After recording a macro I want to run this in line different rows, say for example line no. 50. But whenever I run this It will go to line no.22 and formatting. How to change macro codes….I don’t want to give particular cell number in macro codes. I want to run this in different cells every time. Please advise me…? Regards, Vishu As always, post YOUR coding efforts for comments. -- Don Guillett SalesAid Software donaldb@281.com "vishu" <vishu@dis...