Free Microsoft Excel 2013 Quick Reference

Change Excel cell format date type examples ("14-Mar-01")

Formatting:
A detail which makes the response to the user "not exact/unique":
Take the following path in any cell: Format / Number / Date / Type
Say you have a “single digit” date; example: 3-May-05.
Choose the first of two : “14-Mar-01” what will the format be?
Choose the second, identical: “14-Mar-01”, what will the format be?
50%-50% chance one of them will give you a leading zero, the other will
suppress the zero. The user finds out after he executes the formatting, not
before.

Shouldn’t we use more explicit examples such as: “ 4-Mar-01” and
“04-Mar-01” ?

----------------
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 pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...ic.excel.setup


Post your answer or comment

comments powered by Disqus
Hello,

I am new to this forum. Greetings to all!

I currently have a bug on Excel 2000 (SP3), running on Win. XP SP2.
(both are french versions)

Here goes:

I creat a new excel file using English Canada as regional settings. I
input some numbers and change the cell format to Number, 2 decimals and
with the thousands separator activated and the parentheses (no colour)
for negative numbers.

After I switch my regional settings to French Canada, the format of the
cell is now custom: "# ##0,00 _$_);(# ##0,00 _$)". Not only has the
format changed, the width of the cell is now larger than before, and
I'm stuck with some ######### in all cells that had the specified
number format in english.

The thing is, if I use 0, 1, or 3 decimals (instead of 2), there is no
bug... And when I switch back to English Canada, the format for the 2
decimal cells is restored and the display is ok.

Why, oh why? I couldnt find anything on the web, and thought maybe
someone here had some ideas...

BTW - When I open the same file on Excel 2003 (SP2) I get the same
result...

thank you for any help you might bring...

Louis

--
louisp
------------------------------------------------------------------------
louisp's Profile: http://www.excelforum.com/member.php...o&userid=25880
View this thread: http://www.excelforum.com/showthread...hreadid=511142

Hello,

I am new to this forum. Greetings to all!

I currently have a bug on Excel 2000 (SP3), running on Win. XP SP2. (both are french versions)

Here goes:

I creat a new excel file using English Canada as regional settings. I input some numbers and change the cell format to Number, 2 decimals and with the thousands separator activated and the parentheses (no colour) for negative numbers.

After I switch my regional settings to French Canada, the format of the cell is now custom: "# ##0,00 _$_);(# ##0,00 _$)". Not only has the format changed, the width of the cell is now larger than before, and I'm stuck with some ######### in all cells that had the specified number format in english.

The thing is, if I use 0, 1, or 3 decimals (instead of 2), there is no bug... And when I switch back to English Canada, the format for the 2 decimal cells is restored and the display is ok.

Why, oh why? I couldnt find anything on the web, and thought maybe someone here had some ideas...

BTW - When I open the same file on Excel 2003 (SP2) I get the same result...

thank you for any help you might bring...

Louis

I have a column of 10900 cells with dates of the form
13-May-05. Excel automatically interpreted them as
dates when I imported the data from a text file. Now
I want them to be interpreted as text. The problem is:
when I change the format to "text", the cell content
changes to a number. For example, 13-May-05 becomes
38485.

Hindsight being 20-20, I should have entered the dates
in the form '13-May-50. But it is too late for that
now, I think.

How can I change the cell format to the text 13-May-05
without manually changing all 10900 cells?

I have a column of 10900 cells with dates of the form
13-May-05. Excel automatically interpreted them as
dates when I imported the data from a text file. Now
I want them to be interpreted as text. The problem is:
when I change the format to "text", the cell content
changes to a number. For example, 13-May-05 becomes
38485.

Hindsight being 20-20, I should have entered the dates
in the form '13-May-50. But it is too late for that
now, I think.

How can I change the cell format to the text 13-May-05
without manually changing all 10900 cells?

Hi all !

I am having a little trouble with some of my excel spreadsheets: I am using some more or less complicated excel spreadsheet, where apparently all of a sudden all cells where formated as "accountig", which now looks a bit strange with all the dates and numbers beeing amounts. But not only that, also when I open another sheet (for example by clicking a pivot-table) the whole new sheet is formatted with "accounting" as well.

Is there a way to change excels default format choice !? I wasn't aware of that, but suddenly it feels like . . .

Thank you very much in advance !

When I open a CSV file in Excel the cell formatting appears to be GENERAL.
What this does is that it formats a cell with 'Feb 2008' as a date
('Feb-08').

What I would like to be able to do is set the formatting to TEXT when I open the file so that the data doesn't change.

I am creating an Excel workbook using VB.NET, and have run into a
problem. Excel at times insists on reformatting data that I enter
into cells, e.g., converting "01234" to "1234", and this screws me
up when I need to read the data back. When I run into this problem
using Excel interactively I simply change the cell Number format
from "General" to "Text", but I haven't been able to figure out
how to do this using VB.NET. Here is a code sample:

Dim wb as Microsoft.Office.Interop.Excel.Workbook

[...workbook is created...]

Dim style as Microsoft.Office.Interop.Excel.Style

style = wb.Styles.Add("Style1")
style.Font.Name = "Arial"
style.Font.Bold = True
style.Font.Size = 12
style.Interior.Pattern = Microsoft.Office.Interop.Excel.XlPattern.xlPattern Solid
style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlighLe ft

I use more than one style, and once I've created a style with the
features I want I apply it to the ranges where I am entering data.
Styles also have a "NumberFormat" property, and I would think that
this somehow could be used to set cell format to "Text". But if,
for example, I try

style.NumberFormat = "Text"

all that happens is that I end up with a weird custom format.
Nothing else I try seems to work either. Can anyone tell me how
to do what I am trying to do?
--
John Brock

Slo-mo work, adjusting Excel cell formats. Helps if the format was revealed
in a toolbar or status bar, WITHOUT having to click on menus, submenus. Even
more helpful: show cell format and range of format in status bar or toolbar.
Could be an icon to select for view, but being visible, like page number
and number of pages, in the status bar is even better.
Now, if we could change the range of the format - as in: you try a format -
find it works in that cell - extend the range for the format in the
disclosure in the status bar, or from the icon, or drop down in the toolbar,
or right-click menu, and NOT have to select the range with your mouse scroll.
If it is part of the MS intuitive macro engine, the program could suggest
the range to the end of the page, for starters.
Or: manually type, or select from a suggestion list, the range in the
disclosure window, which would be an interactive window with drop-down.
And: include date formats that DON'T turn the date into an algorithm
result.

----------------
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 pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions

I have attached a small version of my workbook in hopes that it will better illustrate my problem. I have a large data table from which I run several pivot tables each month.

I have a macro that I run to update the pivot table, an example of such a pivot table is in the workbook.

The macro has worked in the past just fine, but for some reason, the formats of my cells have changed so that the default format is now "Date" for any cell that contains only numbers. My focus right now is the Qtr Month field. It is supposed to return 1,2 or 3. So when my macro runs, an input box requests the user to enter the relevant quarter month (1,2 or 3) and updates the pivot table accordingly. Now, the macro does not run, because the actual Qtr Month value is 1-Jan, 2-Jan, or 3-Jan.

I have changed the cell format in the original data table, but it doesn't seem to affect the pivot tables. Even after refreshing. What did I do to make this happen, and how can I fix it?

Slo-mo work, adjusting Excel cell formats. Helps if the format was revealed
in a toolbar or status bar, WITHOUT having to click on menus, submenus. Even
more helpful: show cell format and range of format in status bar or toolbar.
Could be an icon to select for view, but being visible, like page number
and number of pages, in the status bar is even better.
Now, if we could change the range of the format - as in: you try a format -
find it works in that cell - extend the range for the format in the
disclosure in the status bar, or from the icon, or drop down in the toolbar,
or right-click menu, and NOT have to select the range with your mouse scroll.
If it is part of the MS intuitive macro engine, the program could suggest
the range to the end of the page, for starters.
Or: manually type, or select from a suggestion list, the range in the
disclosure window, which would be an interactive window with drop-down.
And: include date formats that DON'T turn the date into an algorithm
result.

----------------
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 pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...heet.functions

I am creating an Excel workbook using VB.NET, and have run into a
problem. Excel at times insists on reformatting data that I enter
into cells, e.g., converting "01234" to "1234", and this screws me
up when I need to read the data back. When I run into this problem
using Excel interactively I simply change the cell Number format
from "General" to "Text", but I haven't been able to figure out
how to do this using VB.NET. Here is a code sample:

Dim wb as Microsoft.Office.Interop.Excel.Workbook

[...workbook is created...]

Dim style as Microsoft.Office.Interop.Excel.Style

style = wb.Styles.Add("Style1")
style.Font.Name = "Arial"
style.Font.Bold = True
style.Font.Size = 12
style.Interior.Pattern = Microsoft.Office.Interop.Excel.XlPattern.xlPatternSolid
style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlighLeft

I use more than one style, and once I've created a style with the
features I want I apply it to the ranges where I am entering data.
Styles also have a "NumberFormat" property, and I would think that
this somehow could be used to set cell format to "Text". But if,
for example, I try

style.NumberFormat = "Text"

all that happens is that I end up with a weird custom format.
Nothing else I try seems to work either. Can anyone tell me how
to do what I am trying to do?
--
John Brock
jbrock@panix.com

please tell me the date type format which is supported by excel 2005.

i'm using xsd also for importing xml into excel and specified date type or
datetime type there in xsd but it is imoprting it as text.giving error this
error it is imorting as text.

the format which i specified is yyyy-MM-dd hh:mm:ss aaa in my jsp which is
throughing xml.

How do I change the default cell format to text, so that when I open a csv or other text file, it automatically shows the data correctly instead of removing leading zeros, turning them into dates, etc. I know to go to data->text and go through the wizard to get it to interpret the file correctly, but this adds overhead every time I reopen a file and ruins my work-flow. I edit a lot of data from databases in Excel, mostly csv files, and it would save a lot of time and trouble to just be able to double click and have Excel open it correctly.

I have a spreadsheet on which I enter the date using "ctrl + :"
On some sheets, I cannot change the format of this entry. It seems that the cells are locked.
On the spreadsheet in which I am presently working, I cannot change the cell format of cell "C1", but I can change the format of Cell "K 1", which is on another page of that same sheet
I have tried going into "Tools / options" to try & find the problem but haven't been able to correct this.
Any help would be appreciated
Thanx

Hi,

Wonder if anyone can help with this problem. I have a worksheet that contains a column of dates that appear in dd/mm/yyyy format. This data is exported from another application. Problem is that they are not formatted as dates. When I check the cell format, it is set to general. Changing the cell format to date makes no difference - the cell contents are still not recognised as being dates.

Is there a way to force the cell contents to be reformatted (and recognised) as dates? There are over 700 of these, and I wouldbn't like to have to manually renenter them all every week!

All help (as always) gratefully received)

Mark

A spreadsheet previously created changes a date entry to "=date typed in" and
displays "01/00/00" no matter what format has been applied to the cell.

A HAVE A TABLE WITH MANY DATES AND I WANT TO CHANGE COLOUR CELL IF DATES ARE
SMALLER OR BIGGER THAN ONE DATE

EXAMPLE:
DATES
12.02.2006
12.03.2008
12.02.2010
13.12.2011
28.07.2003
+.................

AND REFERENCE DATE IS 01.11.2008
CELLS WITH DATE SMALLER THAN 01.11.2009 ---RED
AND CELLS WITH DATE BIGGER THAN 01.11.2009--- BLUE

MANY THANKS !!!!!!!!!!!!

A spreadsheet previously created changes a date entry to "=date typed in" and
displays "01/00/00" no matter what format has been applied to the cell.

Hi i seem to be having a problem formatting the date in my table.

I format the cells in the 'date' category to display the date as '14-Mar-01' but only some of the dates in the cells are changed. I have entered all the dates in this format: 01/01/09. Why do some cells change to the correct format and others do not.

Any help is greatly appreciated.

I need to write a cell format that includes 1/100 of a sec. in Excel.

the only optiona available are yy-mm-dd hh:mm:ss how do I get a better
resolution?

Hi,

I receive an excel spreadsheet of daily orders received which I copy to a new workbook (with a macro) and then run another macro that does some formatting and other things ready for the data to be used in a MS Word mail merge for customer welcome letters and order receipts (don't ask why, I just do it!).

Here's the problem. The original sheet has columns E to H formatted as USD currency ([$$-409]#,##0.00) and even though I use the following code to paste the sheet information to the destination new workbook:

Windows("new_orders.xlsm").Activate   'this is
the original workbook
With ActiveWorkbook.Sheets("orders")
    Lastrow = Sheets("orders").Cells(Rows.Count, "B").End(xlUp).Row
    Range("A1:XFD" & Lastrow).Select
    Selection.Copy
End With

Windows("new_customers.xlsm").Activate   'this is the destination workbook
ThisWorkbook.Sheets("import_customers").Activate
With ThisWorkbook.Sheets("import_customers")
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Range("E:H").Select
Selection.NumberFormat = "@"
End With
I still end up with the destination formatting for columns E to H being of type currency (#,##0.00) although it is now in GBP for some unknown reason..... but I need it as text so that it imports correctly to word mail merge.

How can I change the cell format in VBA so that it is text?

There is a second problem. When the value of the cell is an exact pound amount, e.g. 32.00, if I manually convert it to format type text, it changes the cell contents to 32 when I actually want it to be 32.00.

Any ideas on how I can take a currency format and change it to text without losing the post decimal place digits when they are both zeros?

Any help would be greatly appreciated!

Thanks,

Nick

I have a problem with changing the format of a pure number in a cell
formatted as a text. After the type is changed to a number (actually
it's changed to special - 000000), the cell refuses to change until
after the cell is edited (with or without changes) and the ENTER key is
pressed. Is there an easy way around this?

A similar thing happens with a cell has a special enter character.
Editing the cell and pressing enter changes the cell format to word
wrap and to recognize the special character, though the character is
not usually desired. I know how to delete the special characters with
a find and replace, but it illustrates how excel refuses to change the
cell formats until after you edit each cell. That can take a lot of
time with thousands of entries, like the document database that I'm
working with. Any suggestions?

Thanks in advance,
Andy Landen

Hello guys
i have problem in excel that when copying from sheet to another sheet in the same workbook it takes the format of the cell as time x.xx and applies there, regardless to the cell format of where it it goes. now if applied time-difference formula on sheet 2 it doesn't work! when entering time in sheet 2 it automatically make it as HH:MM:SS AM no matter however tried to change the cell format to time: hh:mm or to customer as h:mm@ it doesn't calculate the time different between 2 cells, one is copy-past from the previous sheet, and the new one which is inserted manually as time but never is taken as the one previously copied... therefore the output sum cell gives "VAlue" even obviously there is 2 cells with "time" but never calculating,

i think it is format of cell and we have to deal with it ? can we we force the cell format in somekind of code or making the copy-paste times from previous sheet comes without the cell format?

obviously this is to be deal with low experience IT stuff employee so should be protected not to change the cell format manually each time, they have tonly to insert the manul part then after that, the formula should caluclate this in protected cell

what you think guys? possible?

Mike

Hi,
is it possible to format a whole column as "hyperlink cells"?
The background is the following:
I want to input data with an Access Form. Therefore, I want to link my Excel-Sheet with Access. But I have problems with entering new Hyperlinks in the Access Form, because the Excel Cell formatting of the Cell is "Text", and Access can't change the Formatting of a Linked Excel-Sheet...

Hope you have a tipp for me...
JK


No luck finding an answer? You could always try Google.