Typing the date into Excel

Hi, and thanks in advance.
I've recently updated from Excel 2003 to Excel 2007.  I'm having a problem 
with entering dates:
(1) I enter a date, say: 5/17/2009 or 5-17-2009 
(2) The cell displays 0.000146 or -2021.
(3) I format the cells as "date".
(4) The cell displays as 1900/01/00 or ##### (indefinite)
(5) The formula bar displays =5/17/2009 or =5-17-2009
(6) It doesn't matter if I format the cells as dates in advance or after 
entering the date.

I'm guessing that there is a setup option somewhere that is wrong, but I 
can't find it.  The documentation, and my experience with earlier editions of 
Excel, suggest that Excel will recognize the date when entered like this.

And advice or help appreciated.
Thank you
Seamus


0
5/18/2009 4:46:01 AM
excel 39879 articles. 2 followers. Follow

4 Replies
368 Views

Similar Articles

[PageSpeed] 33

On May 18, 2:46=A0pm, Seamus Moran
<SeamusMo...@discussions.microsoft.com> wrote:
> Hi, and thanks in advance.
> I've recently updated from Excel 2003 to Excel 2007. =A0I'm having a prob=
lem
> with entering dates:
> (1) I enter a date, say: 5/17/2009 or 5-17-2009
> (2) The cell displays 0.000146 or -2021.
> (3) I format the cells as "date".
> (4) The cell displays as 1900/01/00 or ##### (indefinite)
> (5) The formula bar displays =3D5/17/2009 or =3D5-17-2009
> (6) It doesn't matter if I format the cells as dates in advance or after
> entering the date.
>
> I'm guessing that there is a setup option somewhere that is wrong, but I
> can't find it. =A0The documentation, and my experience with earlier editi=
ons of
> Excel, suggest that Excel will recognize the date when entered like this.
>
> And advice or help appreciated.
> Thank you
> Seamus

are you typing in =3D5/17/2009 ?  or leaving out the =3D ?

=3D5/17/2009 does equal 0.000146 .

I've never known excel to put an =3D in automically, even in 2007

sorry i cant help further

Regards

David
0
dheaton (109)
5/18/2009 6:28:37 AM
On May 18, 2:46=A0pm, Seamus Moran
<SeamusMo...@discussions.microsoft.com> wrote:
> Hi, and thanks in advance.
> I've recently updated from Excel 2003 to Excel 2007. =A0I'm having a prob=
lem
> with entering dates:
> (1) I enter a date, say: 5/17/2009 or 5-17-2009
> (2) The cell displays 0.000146 or -2021.
> (3) I format the cells as "date".
> (4) The cell displays as 1900/01/00 or ##### (indefinite)
> (5) The formula bar displays =3D5/17/2009 or =3D5-17-2009
> (6) It doesn't matter if I format the cells as dates in advance or after
> entering the date.
>
> I'm guessing that there is a setup option somewhere that is wrong, but I
> can't find it. =A0The documentation, and my experience with earlier editi=
ons of
> Excel, suggest that Excel will recognize the date when entered like this.
>
> And advice or help appreciated.
> Thank you
> Seamus

Seamus,

just found something in Excel 2003 menu options.
Its called 'Transition Formula Entry' and setting this on does indeed
add an =3D before a date entry.

I have no idea where you will find this option in 07, but it must be
somewhere

Regards

David
0
dheaton (109)
5/18/2009 6:30:36 AM
On Sun, 17 May 2009 21:46:01 -0700, Seamus Moran
<SeamusMoran@discussions.microsoft.com> wrote:

>Hi, and thanks in advance.
>I've recently updated from Excel 2003 to Excel 2007.  I'm having a problem 
>with entering dates:
>(1) I enter a date, say: 5/17/2009 or 5-17-2009 
>(2) The cell displays 0.000146 or -2021.
>(3) I format the cells as "date".
>(4) The cell displays as 1900/01/00 or ##### (indefinite)
>(5) The formula bar displays =5/17/2009 or =5-17-2009
>(6) It doesn't matter if I format the cells as dates in advance or after 
>entering the date.
>
>I'm guessing that there is a setup option somewhere that is wrong, but I 
>can't find it.  The documentation, and my experience with earlier editions of 
>Excel, suggest that Excel will recognize the date when entered like this.
>
>And advice or help appreciated.
>Thank you
>Seamus
>

Press the "Office" button (big button upper left corner) (opens a dialog box)

	Excel Options (lower bar of that dialog box)
	Advanced
		Scroll down to Lotus Compatibility settings and DEselect the
Transition Formula options.	
--ron
0
ronrosenfeld (3122)
5/18/2009 10:34:34 AM
David, Ron:

Thank you very much.  That was very helpful: the "=" has disappeared, and 
the date appears as I type it; although it is in text form.

And, of course, to make it "intelligent", so that formulae work on it, et 
cetera, I need to enter it the way I have my system options set, that is in 
my case, 2009/05/17 (yyyy/mm/dd). (Gets my file lists sorted "right".)

Between both, I have a rich set of choices.

Again, thank you both.

"Ron Rosenfeld" wrote:

> On Sun, 17 May 2009 21:46:01 -0700, Seamus Moran
> <SeamusMoran@discussions.microsoft.com> wrote:
> 
> >Hi, and thanks in advance.
> >I've recently updated from Excel 2003 to Excel 2007.  I'm having a problem 
> >with entering dates:
> >(1) I enter a date, say: 5/17/2009 or 5-17-2009 
> >(2) The cell displays 0.000146 or -2021.
> >(3) I format the cells as "date".
> >(4) The cell displays as 1900/01/00 or ##### (indefinite)
> >(5) The formula bar displays =5/17/2009 or =5-17-2009
> >(6) It doesn't matter if I format the cells as dates in advance or after 
> >entering the date.
> >
> >I'm guessing that there is a setup option somewhere that is wrong, but I 
> >can't find it.  The documentation, and my experience with earlier editions of 
> >Excel, suggest that Excel will recognize the date when entered like this.
> >
> >And advice or help appreciated.
> >Thank you
> >Seamus
> >
> 
> Press the "Office" button (big button upper left corner) (opens a dialog box)
> 
> 	Excel Options (lower bar of that dialog box)
> 	Advanced
> 		Scroll down to Lotus Compatibility settings and DEselect the
> Transition Formula options.	
> --ron
> 
0
5/18/2009 8:06:01 PM
Reply:

Similar Artilces:

File encryption in excel.
Could someone please advise me how to De- encrypt files in Excel. I Encrypted a file some time ago, but cannot now find any item on the drop down menu's to enable me to de-encrypt. Thanx ...

Pie of Pie Chart type with smaller pie showing slices of main slic
Hi How can I do the following: Lets say I have the following labels and data For the main pie I have A,B,C labels with A =5, B =10, and C =20. So my main pie would show 3 slices with C being the largest slice. To do this I highlight the 3 labels and their data and choose Insert, Pie, Pie in a Pie to show a main pie on the left with 3 slices and a smaller solid pie to the right with series lines connectiing them. I want to show the solid smaller pie on the right in several slices to show how the C slice of 20 is broken down. lets say the main slice C of 20 is made up ...

can't open a Excel file
can't open a Excel file the message is " too many different cell formats" , yes i made many sheets in one Excel file , Please help ! i need open this file. Here's a link that explains your problem: XL: Error Message: Too Many Different Cell Formats http://support.microsoft.com/default.aspx?scid=kb;en-us;213904 And some people have posted that opening in OpenOffice (free), fixing it up, and resaving will fix it. (http://www.openoffice.org, a 60-65 meg download or a CD) gl88 wrote: > > can't open a Excel file the message is " too many different cell form...

Typing in macros
I have made a few macros and use them in word and excel 2000. When I see typed out macros on the message boards I am not quite sure how to use them. Do I go into the editor, type them in and then link them with a button on the toolbar? What module do I put them in and is there one for the personal worksheet. I have been struggling with the syntax of Visual Basics for years. Just need a step by step explanation. Thank You Sue In general, the macros you see in the groups are likely to be ordinary macros that will go into a general module in the VBE, eg:- Hit ALT+F11 and this will...

Excel Rows and Columns Issue
Thanks Anne, I haven't tried it, but I know it will do the trick. Thanks again! Cheers -- nexceluse ----------------------------------------------------------------------- nexceluser's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1507 View this thread: http://www.excelforum.com/showthread.php?threadid=26712 ...

Open File Dialog to fileter multiple file types at once...?
Hello, Here is another quick one for you all. The solution is probably simple, but I cannot find the answer anywhere. I have the following code: CFileDialog OpenDlg(TRUE, NULL, NULL, OFN_ALLOWMULTISELECT | OFN_HIDEREADONLY, ""); // Later must set this to retrieve a value from the registry OpenDlg.m_ofn.lpstrInitialDir = "c:\\"; OpenDlg.m_ofn.lpstrFilter = "txt Files\0*.txt\0Doc Files\0*.doc\0"; OpenDlg.DoModal(); I am trying to get the box to filter out everything but the *.doc and the *.txt files ... I have seen some boxes that have something that...

Excel should have semi-log graph plotting
excel should have charts that use semi log graph paper ---------------- 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/community/en-us/default.mspx?mid=7e3181d9-05f2-46a3-92cb-e2c8e421ee1f&dg=microsoft.public.excel.charting For a log y-axis, create ...

Publisher will not print, but Word and EXCEL do
I have a printer problem using MS Office Publisher 2003. My friend just installed a Brother MFC-3820CN USB printer to his Windows XP Home (V2002 SP2) computer. He can print from his Word and EXCEL (2003) programs, but not his Publisher (2003 SP1) program. When the “Print” function is selected in Publisher, the following message appears: “A printer has not been installed on the system. Install a printer for Publisher to use. The fonts in your publication may look different as a result of the printer selection.” The printer functions fine in all the other programs. Currently, Publisher ...

Rectification of wrong posting type
Hi There is one PL account say "Mic Expenses" has got wrong posting type "Balance sheet", when we closed the year 2005 ,System has brought forward the balance of that account into next year 2006, which otherwise should have been gone into the Retain Earning of 2006, is there any option to rectify this error, other then passing rectifying JV in 2005(coz in that case if I take last year TB then ending balance of this account would be Zero) or restoring the backup and doing closing procedure again. Thanks in Advance Shabnam There is a Article ID for this in partner s...

save same web query by date so I can chart currency movements
I'm fairly new to using Excel for financial modeling, but recentl figured out how to pull graphs from the internet and have them updat periodically (Excel asks you to choose a number of minutes betwee updates). However, I really don't want the web query to update, I want it to sav the chart each day (automatically instead of manually) in a ne worksheet so I can go back and develop a page that will chart changes. If it helps to visualize, I'm pulling a currency chart fro Bloomberg.com, but they only list the day's movements. I'd like to hav it save each day at market clos...

Automatic date insertion
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I know I've done this before but I just can't remember how. I've tried every option I can think of and searched the archives but still can't do it. <br><br>I want the current day's date to always appear on a document, preferably in the bottom right. I don't want to add this to the Normal Template as I don't add the date to all documents. <br><br>Thanks so much for refreshing my faulty memory. <br><br>Sheri Hi Sheri: Inset>Field>Date... ...

Excel ODBC and dates...
Hi all, I'm using a label printing prog (Seagull - Bartender) to print some labels using ODBC data from Excel. (Win 2K and Office 2K - using "user DSN" for XLS files) Now dates appear OK in Excel ie: dd/mm/yyyy But when the data is accessed with ODBC they appear as follows: yyyy/mm/dd hh:mm:ss Help.. any ideas what's going on here? Regards, Doz ...

Connection from Excel to Access
I am developing an application in Excel that reads data from and writes data to an Access database. I am using VBA and have experimented a bit with various connection strings - without really knowing what I'm doing. Could someone please point me to some usefull examples? - or perhaps show the best (most reliable and fastest) connection string to use? Thanks in advance! Helge On Nov 25, 10:27=A0am, "Helge V. Larsen" <Helge.V.Larsen_Remove_this@Risoe_Remove_this.DK> wrote: > I am developing an application in Excel that reads data from and writes d= at...

Some chart types cannot be combined with other chart types?
Hi, I am getting the above error message everytime I try to insert a chart or any type into a completely blank presentation using either the Insert, Chart command or the Insert Chart button in the Title and Content slide layout. I am using 2007. Any help or suggestions would be appreciated. TIA, Shane Devenshire Try running Office Diagnostics, Shane. Open PPT or Excel, hit the Office button, then <app> Options | Resources. I think that may resolve this issue. -- Echo [MS PPT MVP] http://www.echosvoice.com What's new in PPT 2007? http://www.echosvoice.com/2007...

I am looking for a Excel program that I can log my vehichles.
I am looking for a Excel program that I can log my vehichles. The maintenance, fuel, ect. Ok, you're on the right track. On row 1, just type your headings in... Date | Mileage | Fuel (gals) | Fuel ($$) | Maint./Notes ....basically whatever you want to keep track of. Then, just start entering your data. "Layne" wrote: > I am looking for a Excel program that I can log my vehichles. The > maintenance, fuel, ect. How about a Vehicle Service Record template? http://office.microsoft.com/en-us/templates/TC061061511033.aspx Gord Dibben Excel MVP On Thu, 6 Oct 2005 10...

need help to declard type=IntType instead type="xsd:int" with xml schema
Hi, I am writing C# code to produce xml schema. //Create attribute XmlSchemaAttribute att2 = new XmlSchemaAttribute(); att2.Name = "CustomerID"; att2.SchemaTypeName = new XmlQualifiedName("int",schemaNS); att2.Use = XmlSchemaUse.Required; ct.Attributes.Add(att2); I need to produce schema with custom data type instead of the xsd:string or xsd:int The code above will produce the following schema <?xml version="1.0" encoding="utf-8"?> <xsd:schema attributeFormDefault="unqualified" version="1.0" xmlns:xsd=...

Adding amounts based on a conditional date
How can I sum a range of amounts, each of which has a respective date associated with it, based on whether or not that date is less than or equal to a cutoff date (i.e, add together all the amounts that occured between 10/1/05 and 10/31/05? Hi One way, assuming your dates to be in column A and your amounts in column B =SUMPRODUCT(--($A$1:$A$100>=DATE(2005,10,1)),--($A$1:$A$100<=DATE(2005,10,31),B1:B100) Change ranges to suit, but do ensure they are all of equal size. Regards Roger Govier ruleb wrote: >How can I sum a range of amounts, each of which has a respective date &...

difference of dates
How can I check for entries made during the last week, what would I put in for the query. I do have a field where it stores the date. Also how would I get a report for all my data broken down into weeks? Week of 8/1 week of 8/8 etc. Thanks, Greg Blanche On Mon, 13 Aug 2007 05:56:05 -0700, GregB <GregB@discussions.microsoft.com> wrote: >How can I check for entries made during the last week, what would I put in >for the query. I do have a field where it stores the date. There are a number of date handling functions built in to Access. Just what do you mean by "the last ...

Excel 2007 "Protect Sheet" option not available
Hi there, In Excel 2007 I tried to protect certain ranges. I don't know exactly what I did, but suddenly the "Protect Sheet" option is no longer available (it's of course still there but cannot be selected, only "Protect Workboo" is selectable). I cannot figure out how to unlock the Protect Sheet option. Any ideas? Fabz Oh, this thread solved my problem: http://www.pcreview.co.uk/forums/thread-1039062.php When your sheets are "grouped" in the Excel Title Bar one can find the Title "MySheet.xlsx [Group] - Excel 2007". ...

Formula for calculating # of days between dates minus a particular date if present
I'm trying to create a formula that will calculate the number of days between two dates automatically subtracting a day(s) from the equation if a specified date(s) falls in between the two dates. For example, I don't want 1/15/04 to register as a counted day. If I enter the dates 1/1/04 in one cell and 1/12/04 in another cell then it's a straight foward DATEDIF equation. But if I enter 1/1/04 and 1/28/04, then I want the formula to figure out the number of days between 1/1 and 1/28 automatically subtracting 1/15 from the equation. This gets trickier as I need some calucations th...

outlook type interface
I have an SDI application and want to change it so I have a split window, on the left I want a TreeControl above a 'PropertyGrid' control. Then my exisiting SDI Client in the space on the right Can anyone point in the right direction to achieve this Any help will be much appreciated Thanks Phil You need to use two CSplitterWnds (as members of your CMainFrame). Override CMainFrame::OnCreateClient and create a vertical splitter as the main child (i.e. 1 row, 2 cols). Then you want to create a horz splitter as a child of the left-hand pane of the main splitter (which has 2 rows, 1 c...

Excel true date formulas
I need to know how I can enter a date , 02/28/06 in one cell and then i another cell tell me if that date is true. It is to be used when there is a list of contracts, in one cell th date of expiry, and them another cell if that date has come to pass o not happened yet(expired, current) Will the "if" function work with real date -- spyde ----------------------------------------------------------------------- spyder's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3200 View this thread: http://www.excelforum.com/showthread.php?threadid=51734 how about ...

Forgotten Excel password
Does anybody know how to unlock an excel doc if I have forgotten the password i set for excel, 2007 version? Thanks "ashsud" <ashsud@discussions.microsoft.com> wrote in message news:770B68E6-9D0D-4F6E-8B3E-B42CC7B4A736@microsoft.com... > Does anybody know how to unlock an excel doc if I have forgotten the > password > i set for excel, 2007 version? > > Thanks Excel Utilities may be able to help although I haven't used it with Excel 2007. http://www.appspro.com/Utilities/ExcelUtilities.htm V ...

Scatterplots with different colors based on Type of Data
Is it possible to plot a scatterplot with different colors for 2 sets of data? E.g Type of Agreement MRP Discount% Limited 10 5% UnLimited 20 10% I want to show different colors for Limited & Unlimited. Is it possible to plot scatterplots on both axis(Primary & Secondary) Please let me know. Thanks, Satish If I use this range to make a chart Limited 10 5% Unlimited ...

cells changing from number to date
I am putting a number in a cell, then tabbing to the next cell, the previous cell changed from a number to a date. How do I keep the number there? What are you inputting, a fraction?if so, format as # ??/?? -- HTH RP (remove nothere from the email address if mailing direct) "mwhite@srgmac.com" <mwhite@srgmac.com@discussions.microsoft.com> wrote in message news:E71CED6D-8370-44D1-A4BF-9A6B352205F3@microsoft.com... > I am putting a number in a cell, then tabbing to the next cell, the previous > cell changed from a number to a date. How do I keep the number there? ...