Date Formats don't work

I have an Excel spreadsheet with a column of dates in the MM/DD/YYYY format.  When I click on Format, Cells and Choose Category of Date and set it to the YYYY-MM-DD format, nothing changes.  In fact if I set it to any date format, the format of the cells do not change.  If I enter a value of '05/01/2003' and choose date format of YYYY-MM-DD, it does not change the format.  What gives

My regional settings are US, but I do have my date format settings as 'YYYY-MM-DD' with the hyphen as the separator.
0
anonymous (74722)
2/16/2004 3:46:10 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
276 Views

Similar Articles

[PageSpeed] 34

The values are probably text

-- 

Regards,

Peo Sjoblom


"TdyYrLove" <anonymous@discussions.microsoft.com> wrote in message
news:72A37294-EFE0-475C-9DB9-0506F24C682C@microsoft.com...
> I have an Excel spreadsheet with a column of dates in the MM/DD/YYYY
format.  When I click on Format, Cells and Choose Category of Date and set
it to the YYYY-MM-DD format, nothing changes.  In fact if I set it to any
date format, the format of the cells do not change.  If I enter a value of
'05/01/2003' and choose date format of YYYY-MM-DD, it does not change the
format.  What gives?
>
> My regional settings are US, but I do have my date format settings as
'YYYY-MM-DD' with the hyphen as the separator.


0
terre08 (1112)
2/16/2004 4:16:29 PM
>-----Original Message-----
>I have an Excel spreadsheet with a column of dates in the 
MM/DD/YYYY format.  
> When I click on Format, Cells and Choose Category of 
Date and set it to the YYYY-MM-DD format, nothing 
changes.  
> In fact if I set it to any date format, the format of 
the cells do not change. 
> If I enter a value of '05/01/2003' and choose date 
format of YYYY-MM-DD, it does not change the format. 

Are you sure that the format has been set to Date before 
you entered the values? Otherwise, it might be that the 
value is stored as Text, and in that case no change of 
format will help. To translate a Text column to Date, 
select the whole column, and do Data / Text to 
Columns... / Delimited (you do not need choose any 
delimiter, because you need just one column) / Date(MYD)

hope this helps
0
anonymous (74722)
2/16/2004 4:27:07 PM
The Text to Columns does the trick

It appears to be a problem with an incorrect date format.  The cell is defined as a date column with a format of YYYY-MM-DD.  So if I enter 03/01/1999, it does not recognize that as a date (1999 is greater than 31).  The format will not convert one order to another (MDY to YMD), but it will convert format (1999/01/01 to 1999-01-01)

Thanks for the help!
0
anonymous (74722)
2/16/2004 11:36:08 PM
Reply:

Similar Artilces:

Working with Excel #2
Dear friends! I have the following problem - I am needing to represent in Excel a number with 20 (or more) digits. How can I do this? Many thanks! Hi Igor2005, > I have the following problem - I am needing to represent in Excel a number > with 20 (or more) digits. How can I do this? > That is only possible with either: - an addin (search google, there should be something for this, I recall seeing it but misplaced the link). - or by formatting the cell as text (but you won't be able to do math with the number easily then) Regards, Jan Karel Pieterse Excel MVP www.jkp-ad...

Cannot compose email in html format only plain text
I have some sort of html problem as I can't insert text into any text boxes in explorer. I also can't highlight text and in outlook I can't enter text into an email in html format, however in plain text I can. I have installed the new XP sp2 update, I have used the detect and repair option in Outlook, I have tried running explorer from another pc on the network on my pc all to no avail. So I am guessing that it is a problem caused externally of explorer and outlook. And I have also ran a fully updated virus check. Any help would be much appreciated as this is driving ...

Reports by date
I'm trying to get reports based on a user input date. I don't want all the records in the database just the entries from a certain date and newer. Could someone please help? Thanks ...

Covered calls getting exercised not working properly
I'm using MS Money 2006 Premium edition and it's missing a key piece of executing a covered call. I can enter the initial sale of the call itself without a problem (sell to open) but if the call gets exercised, MS Money doesn't let me chose which shares will be called away. So, if I sold one call (represents the potential sale of 100 shares) in MSFT and I own 200 shares of MSFT (purchased in 2 lots of 100), if the call is exercised and I enter the Activity "Exercise," Money goes through all of the proper steps except it doesn't prompt me for which 100 share...

UPPER CASE formatting a cell
I receive excel data from a number of sources to be combined together in a single sheet. I would like to format a column of cells to be all upper case but not use the =UPPER() function. I'd like to format a cell to force any lower case char to upper case automatically when data is entered. Custom formatting as >CCCC doesn't work. Can this be done? Conversely, how can I force cell A1 to be UPPER CASE by using =UPPER(A2), then remove the reference to A2 when printing the result? I don't want the cell A2 to print. This I forget how to do. Hi you'll need VBA for this. ...

Outlook 2002
I use outlook 2002 on a Windows NT SP6a machine. While using outlook, i have noticed that sometimes the keys c, v, t and h stop working. however, i can use them in notepad. i can also use them in outlook if i put the caps lock on and use the shift key. if i restart outlook, the problem goes away. sometimes the problem goes away as well on its own without having to restart. any idea what is happening? language is set as english (Australia) ...

Outlook doesn't open in the inbox work space. Open up in the Outlook today works
How do I make the Inbox as the default workspace in Outlook 2000 corporate mailbox. running on Win xp. ...

Budget dates
Is it possible to customize the dates in the budget, instead of having calender months? e.g. 010106 to 020606 Sorry no. -- Regards Bob Peel, Microsoft MVP - Money For UK tips & fixes see http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny. I do not respond to any emails that I have not specifically asked for. "Boenerge" <Boenerge@discussions.microsoft.com> wrote in message news:64F0F8BF-B2A0-4E32-94EC-DE6DA5DEA798@microsoft.com... > Is it possible to customize the dates in the budget, instead of having > calender months? e.g. 010106 to 020606 ...

Text cell complains about date
I have a column containing ratios like 3/4, 3/0, 5/2, etc. I have them all formatted as text. All of the cells that have a zero as the second number (3/0) get a little green triangle and a warning that I have entered a "date string with only 2 digits for the year". Huh? How is "3/0" a date string with 2 digits for the year? And, why is it complaining about a text field? More importantly, how do I get Excel to stop being so "helpful" and leave my damned text data alone -- unexamined? Remove the warning in Options, Tools>Options>Error Checking in 2003...

Outlook 2003 :: Views :: Automatic Formatting
Hello all. I have two questions about views. Hope you can help me. 1. How to higlight messages from the certain domain? In the dialog "Automatic Formatting" I can define condition to check From field (for example, "where From field contains @microsoft.com"). But the view checks only sender's name (not e-mail). However, I can filter by e-mail using From field in the rule (Rules and Alerts). What are the differences? Because of internal interfaces, I guess. Rule can access to "source data" but view can access to "prepared- to-view data" only. I could ...

Date Logging via Workflow
It's not enough in my company to simply know what Sales Stage a given Opportunity is in, but what date the new stage began. We not only need to know how long the Sales Cycle took, but how long between Stages. Please allow for Workflow date logging with Sales Stages. Better yet, Please allow for Workflow to update date flields. Even better, allow work flow to trigger JScript. --Dodd ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message...

CTL-END not working as expected
CTL-END should select the last cell in a worksheet. I have a workbook that contains a worksheet used to store transactional data. The 12/31/08 sheet had 15401 rows of data and CTL-END always selected last cell O15401. Each week, I copy and paste the new transactional data from one workbook to the worksheet in my master. The latest 2009 data worksheet has 726 rows of data and CTL-END properly select O726 within that sheet. However, after copying and pasting (using CTL-A to select all data in the source and destination sheets, then pressing CTL-Cin the source sheet and CTL-V in the desti...

Auto date changing in Excel is maddening
I'm using MS Office Excel 2003 (11.6113.5703) to post data that spans over several days. sample plot http://peswiki.com/index.php/Directory:Bedini_SG:Replications:PES:Sterling_Allan:Data:Exp10.4_Different_Standing_Discharge_Rates or try http://tinyurl.com/6f8zn We had to jump through all kinds of work-arounds in order to accomplish this. It was not a straight-forward process, though it should have been. When I enter the date format, for example, as Format > Time > 3/14/01 13:30 And then enter 11/17/04 0:30:30, it enters okay, but when I double click on the cell to edit it, Ex...

Excel can't SAVE added data & formatting?
Excel 2000 ... I have a single page spread sheet (my own) that was working fine. After expanding the spread sheet to handle more equipment I received error message stating: Excel can not SAVE all of the added data & formatting ... Here I had the option to select OK without fixing ... CANCEL ... or ... HELP. I selected HELP & ended up on a blank HELP Page ... So now I am turning to this board. What gives? Could I be running into a formatting limit issue? Sheet less than 1000 rows, but many formats ... Thanks ... Kha "Ken" <anonymous@discussions.microsoft.com...

Convert Excel records to text documents in arbitary formats
Hi, A newbie here (groan). I need a tool to take data in excel tables and generate text documents with the data rearranged in new forms, eg to look somewhat like mailing labels (but with more data items, like email address). Is there a tool that will take Excel data and generate documents in arbitrary formats? Also, is there a tool to edit column data, eg. the Name field I have is all uppercase, and I wanted to cap the first and make the others lower case (eg: JONES -> Jones), a pain in the ass if done manually. Generally, an editing tool for column data. thanks in advance Don't kno...

long datatype to date string
Hello, In my cell I have a long datatype value, like 1046101500. Now I want to have this converted to a date string like 'dd/m'/yyyy hh:mm:ss', so my example would be converted to '24/02/2003 15:45:00'. Is this possible? Andy Candy How does your data type correspond to the date? Where is the year? Month? etc. Andy. "Candy" <andy_hilvenNOSPAMPLEASE@hotmail.com> wrote in message news:3f729cad$0$814$ba620e4c@reader2.news.skynet.be... > Hello, > > In my cell I have a long datatype value, like 1046101500. Now I want to have > this converted to...

Export to CSV format
Dear All, I'm using Office 97, I've 2 macros : a) hide the rows with amount valu zero and b) automatic save the sheet to local drive with csv-extension. But, when the sheet is saved I noticed that in the ne file the hidden rows are still visual. How to avoid these lines ???? Used code (a) Hidden rows : Sub hide_rows() Dim RowNdx As Long Dim LastRow As Long LastRow = ActiveSheet.Cells(Rows.Count, "W").End(xlUp).Row For RowNdx = LastRow To 1 Step -1 If Cells(RowNdx, "W").Value = 0 Then Rows(RowNdx).Hidden = True End If Next RowNdx End Sub Used code (b) save to c...

My Tasks (tasks.aspx) doesn't work on my machine, but does on othe
It would appear that something on my machine has broken. When I go to the homepage of our project server site it won't even load if the My Tasks webpart is on the page. If I remove it and reload the page everything works fine. All of the other users in my dept are able to get to the page whether My Tasks is on the page or not. Any thoughts? I am the admin (I know, I know, I should be able to fix this, I'm the admin) so I have rights to login and make server level changes, just need help figuring out what is wrong. Since it's only on my machine I assume it has ...

x-axis dates
I have set up my line chart in 2007 with dynamic paramaters (names) and since then the date catagory is controlled by horizontal(values). The axis values are numbers even though the labels appear as dates. I have to use the microsoft numbers to determine the axis start and end limits. What happened? -- GeneS Perhaps I can better explain my problem. When I select the x-axis to reformat instructions are that I should be able to change axis type. That assumes I have horizontal (catagories) listed. I have horizontal(values) and there is no type to change. -- GeneS "Gene" ...

About formatting books for professional printers
My book's page size is 5.5" by 8.5"I was told by Lighning Source, "Do not send in spreads, send sequential order first to last page, on single page document." When I usually print my Publisher file to pdf, it defaults in spread mode. (I can view it as a single page but of course that's only a view command). After I change to portrait from landscape in page setup, I see now when I print to PDF, it is not printing centered, and it has crop marks, neither of which is what LSI wants. They want centered printing with no crop marks. Is this a problem that can be solv...

convert text to number format
Hello, I have a column of numbers that have the comment "The number in this cell is formatted as text or preceded by an apostrophe". I'd like to convert all these these values to numbers so I can get a summation. By mousing over the cell I can get a menu that allows me to convert the numbers one at a time, but how can I convert them all at once? I've tried converting with the Format/Cells and Edit/Copy/PasteSpecial/values. Thanks in advance, Ellen I found the answer: In an empty cell, enter the number 1. Select the cell, and on the Edit menu, click Copy. Select the...

Link a button to a specific format
On my Excel 2003 I can use the Euro (?) button to format the current selection in Euro format. I now wish to replace the Euro currency symbol with my own symbol, i.e. "Fr.* ". How can I link the Euro button to this format? There is no need to change the face of the button itself - I'm happy to leave it as the ? button. By "button" do you refer to a key on the keyboard or the menu icon? best wishes -- Bernard Liengme MVP Excel http://people.stfx.ca/bliengme "Pegasus [MVP]" <news@microsoft.com> wrote in message news:ekBb3VI6JHA.1716@TK2MSFTNGP03...

Hyperlinks are working in Outlook 2007
I´ve succesfully upgraded from Vista Ultimate to Win7 anmd installed Outlook 2007. When I click on a link in an email - the hyperlinks ae working directly. I.e. i have to copy the link to the Clipboard and then open IE8.. It gives an error message: "This act has been cancelled due to restrictions on the laptop. Please contact the system administrator" (that is me :-) ) I´ve linked IE8 to standard programs and OU2007 as my standard email reader. What is wrong, and how can I correct this problem? Thank you in advance. -- Yours Dan Andersen This is a probl...

Drop-down boxes stopped working
I have several drop-down boxes in a file that have stopped working. I used them on Friday. I have not changed the location of the data referenced by the drop-downs. As far as I know, I did not make any setting changes to cause them to stop working. What would cause this to happen? Thanks If the drop down boxes were created from data validation, I've seen a problem with freezing frames. That is, with frames frozen, my data validation drop downs did not work. Art "Dave" wrote: > I have several drop-down boxes in a file that have stopped working. I used > th...

Problem getting RPC over HTTP to work. #2
Setup: Two front-end NLB OWA servers with Exch 2003 SP1. Clustered back-end servers with Exch 2003 SP1. Global catalog on seperate server. I've tested the connection from the outside to the https://webmail.domain.com/rpc and it connects as it should. I followed all the steps in the following document and I'm still have connection problems but there is a question that I have in part of this document- http://support.microsoft.com/kb/827330/. Step 8: Verify the Exchange 2003 port configuration. I ran rpcdump /s exchangeserver.domain.com /v /i. The exchangeserver.domain.com is the back ...