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?
0
Prof
10/13/2009 5:50:28 AM
excel 39879 articles. 2 followers. Follow

4 Replies
827 Views

Similar Articles

[PageSpeed] 5

Remove the warning in Options, Tools>Options>Error Checking in 2003, Office 
Button>Excel Options>Formulas>Error Checking Rules in 2007.

-- 
__________________________________
HTH

Bob

"Prof Wonmug" <wonmug@e.mcc> wrote in message 
news:9058d559d5sd0uc7m1cr3bqskfhre818es@4ax.com...
>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? 


0
BobNGs (423)
10/13/2009 8:43:28 AM
Still doesn't address the silliness re the message about '3/0 being a date 
string with a 2-digit year!
Bob Umlas

"Bob Phillips" <BobNGs@somewhere.com> wrote in message 
news:eiwdvF%23SKHA.4364@TK2MSFTNGP04.phx.gbl...
> Remove the warning in Options, Tools>Options>Error Checking in 2003, 
> Office Button>Excel Options>Formulas>Error Checking Rules in 2007.
>
> -- 
> __________________________________
> HTH
>
> Bob
>
> "Prof Wonmug" <wonmug@e.mcc> wrote in message 
> news:9058d559d5sd0uc7m1cr3bqskfhre818es@4ax.com...
>>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?
>
> 


0
rumlas (268)
10/13/2009 1:36:23 PM
On Tue, 13 Oct 2009 09:43:28 +0100, "Bob Phillips"
<BobNGs@somewhere.com> wrote:

>Remove the warning in Options, Tools>Options>Error Checking in 2003, Office 
>Button>Excel Options>Formulas>Error Checking Rules in 2007.

That works. I wonder what the reasoning is for doing any validity
checking on text data. If I wanted it to be checked as a date, I'd
format it as a date. Alternatively, if they must provide this option,
the default should be OFF.

Thanks
0
Prof
10/13/2009 2:37:45 PM
Well, you are more than welcome to field that pat if you wish.

-- 
__________________________________
HTH

Bob

"Bob Umlas" <rumlas@kpmg.com> wrote in message 
news:uQDnpoATKHA.1792@TK2MSFTNGP04.phx.gbl...
> Still doesn't address the silliness re the message about '3/0 being a date 
> string with a 2-digit year!
> Bob Umlas
>
> "Bob Phillips" <BobNGs@somewhere.com> wrote in message 
> news:eiwdvF%23SKHA.4364@TK2MSFTNGP04.phx.gbl...
>> Remove the warning in Options, Tools>Options>Error Checking in 2003, 
>> Office Button>Excel Options>Formulas>Error Checking Rules in 2007.
>>
>> -- 
>> __________________________________
>> HTH
>>
>> Bob
>>
>> "Prof Wonmug" <wonmug@e.mcc> wrote in message 
>> news:9058d559d5sd0uc7m1cr3bqskfhre818es@4ax.com...
>>>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?
>>
>>
>
> 


0
BobNGs (423)
10/14/2009 7:59:55 AM
Reply:

Similar Artilces:

Date in Access
Is it correct to use next formula: 12/14/1999+ 12/14/2001? I’ve got result = 11/28/2101. What is wrong? You might want to ask in an Access forum and explain the context in which you're using that formula. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx "ER" <ER@discussions.microsoft.com> wrote in message news:69ABCC01-1D28-4A45-8217-10FACA32228D@microsoft.com... > Is it correct to use next formula: 12/14/1999+ 12/14/2001? > I&...

convert a date
I have a field Trans date with different formats, some are DD/MM/YYYY and some are DD/MM/YYYY/HH/MM/SS. I want to use the Between begin date and End date. How do I convert all the date to the format DD/MM/YYYY/HH/MM/SS Please help. pon Formatting has nothing to do with how the data are stored. It only presents the data in the specified format. It is possible some of your records may have date and time and others only time. It should not be that way, but it is. If you are trying to compare on date only when there may be time in the field, you can format the values in the query so you ...

Link in Text box
I have text box links to data in the worksheet in 2003. When I open the file in 2007 the links do not update. How can I get 2007 to automatically update the text boxes without reestablishing the links? ...

Sheet Retains Deleted Text Box Contents on HTML Export
My apologies if this situation has popped up before and been answered... I have an Excel 2002 workbook of some 15 worksheets. When I first created it, I used text boxes as footers -- each sheet was no longer than a page when printed, so one text box per worksheet was enough, and the use of text boxes allowed me to see the footer information without invoking the print preview. Later, I deleted all the text boxes and used the custom footer option instead. Recently, when saving those sheets as HTML files, I found that the data that had been in those text boxes was appearing in the HTML c...

Multiple choices for a cell
I am creating a worksheet which deals with employee or job candidates qualifications. Each cell will hold the various details pertaining to a person's qualifications. Different people have different degrees. This will be all stored in a single cell and separated by a comma. Is it possible to create some sort of custom or drop down box which contains controls or check boxes so as to create a standardised list of qualifications the data entry clerk can choose from? For example, i would like a cell to have a drop down box, which is a field that displays the qualifications a particular gra...

help with spliting data cells
I am looking for assistance with splitting data between cells. I have a column of data (first (space) last name). I need to split that column into two seperate columns so each name value is in it;s own cell and i end with 2 seperate columns. (john smith) to (john) (smith) Please advise if this is easy and can be done. Thanks, Phil pcavalcanto@Aol.com -- pcavalcanto ------------------------------------------------------------------------ pcavalcanto's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=32212 View this thread: http://www.excelforum.com/showthread...

Select range for function in a cell
Hi, is it possible to select create a formula in a cell. such as =SUM( and have the cursor be between the () so the user can select th appopriate range to enter? Or would this just be done separately. (Since one would need to know the range and WHERE to put the formul in..ie. what cell) similar to if you hit the autosum button but ther are no numbers above or the left, it simply waits for the user t select a range and then hits enter. THanks! -- Message posted from http://www.ExcelForum.com Hi if I understood you correctly: No -- Regards Frank Kabel Frankfurt, Germany > Hi, is it p...

Copy and Paste in Excel, copies cell and formula, but shows same v
Excel 2003 with 2007 converters installed. Not constant problem, but have seen it once before, can't remember how to stop it happening. I have a bank forecast which I have a running total column, against a individual line item list. Noddy stuff. Problem. If I make changes to the forecast I need to recopy down the running total formula to refresh the running total when it doesn't pick up the additions, usual when a cut has been pasted in. Whilst it allows me to dragdown or copy and paste the formula and the new cells take on the correct line/column numbers to do thei...

Removing Spaces from Text #2
Thanks Andy! It worked like a dream. Satyajit Thanks for the feedback! -- Andy. "Satyajit Bhagwat" <satyajit@team-infinity.com> wrote in message news:ep9WdFJdEHA.2520@TK2MSFTNGP12.phx.gbl... > Thanks Andy! > > It worked like a dream. > > Satyajit > > ...

displaying a comment only when the cell is selected #2
I'd like to insert a comment in a certain cell, so that when that cell is selected by a user, the comment is then displayed. I know this must be possible, but I haven't been able to figure it out.... I can show or hide comments in various combinations, but can't find a way to hide the comment UNTIL the cell is selected, and then display it. How do I do this please?! Thanks Hi Jeff, Try: Tools Options | View tab | select 'Comment indicator only" | OK --- Regards, Norman "Jeff" <no_em@ilplea.se> wrote in message news:JdidnecOi7ehGVbfRVnyhg@eclip...

Last Cell #2
I know this has been asked before but I cant find what I want on Google. I need to get the address or row number of the first empty cell in column A, either will do, I need to use VB to copy and paste data from ten sheets into one. A formula or a VB way would be great, anyway of identifying the first empty cell. This cell may be empty or contain a null string. Any help would be reaaly good. TIA Alan. to find the last row x=cells(rows.count,"a").end(xlup).row+1 to find the next empty x=cells(1,1).end(xldown).row ' or address -- Don Guillett SalesAid Software donaldb@281.com &...

duplicate cells linearly?
Hello all - Is there a way to duplicate a formula in cells were the row reference grows linearly, instead of simply the number of cells? For instance, if you have in Sheet2 the formula Sheet1!$c1 and you duplicate it into rows below, you get Sheet1!$c2 Sheet1!$c3 Sheet1!$c4 Sheet1!$c5 However, we would like to do : Sheet1!$c4 Sheet1!$c8 Sheet1!$c12 We have a spreadsheet composed of two worksheets. In the first worksheet there are cells which are summed every 72 rows. We want those summed values to appear in worksheet 2, in one row after the other. Since we have a lot of summed cells, i...

How do I stop numbers from converting into dates?
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel I want to enter 4/1 or 4-1 into a cell but every time I press enter it converts it to 1-Apr. this is very irritating and despite looking in the the various preferences and format tabs I have yet to find a way to stop it. What am I doing wrong? First of all, any formulas need to be preceded by an equals sign (=) to let Excel know that what follows is an 'instruction' to be executed. Otherwise the content is interpreted as a text string or -- as in this case due to the operators you're using ("/&quo...

Cell Not working
I have office 2002, my windows is home xp. Last week we had to reformat my xp, got a hold of a nasty worm! I just use a chart to display numbers in series. Now when I try it from an old sheet it inserts the number as a date? I keep trying to change the cell to GENERAL, but it dosen't work. Can't understand it. When you say you keep trying to re-format the cell as General, what result do you get? If I do it on a date cell (re-format) I get the date code (i.e. the value of the date). Ex. cells has "03-01-11", select cell and re-format to General = 37632. -- Regards; Ro...

delete row if given cell = 'X'
Is there an easy way to do the above. Thanks much Carl "MSNews" <nf@no.com> wrote in message news:%23Fj6fBvPKHA.764@TK2MSFTNGP02.phx.gbl... > Is there an easy way to do the above. > > Thanks much > > Carl > I'm not an expert but use data filter to select the rows, select and delete them. Try it with a trial sheet first though! Peter Several ways 1. data>filter>autofilter>filter on the x>delete 2. a looping macro from the bottom up sub delifx() for i=cells(rows.count,"a").end(xlup).row to 2 step -1 if lcase(cells(i,&qu...

using two cells 1 with total time n 1 with date how can i put the.
I'm trying to put the amount of time spent in to a seperate sheet (Cell # 2 column E ) from a sheet that has the dates in one cell (column A) and the time spent in another (column K) and the reason for the time spent in column B. On the seperate sheet I have to put the amount of time spent in column E and match with the date I have in column A on the seperate sheet to populate the chart that I'm making. The only thing that I have been able to do is create a formula to show the amount of time spent and for what reason but i can't match it to the date because the dates ...

print title page setup/sheet can not enter cell in row/column lin
On page setup in the sheet page we can not enter cells into the print titles row or columns. Also the worksheet cell query on these lines will not open. Hi Are you trying do this from Print Preview page, or File|Page Setup? I think it's only possible from the latter. -- Andy. "don r" <don r@discussions.microsoft.com> wrote in message news:26C400D7-14A0-41F8-BBE6-8DF2F5579A30@microsoft.com... > On page setup in the sheet page we can not enter cells into the print > titles > row or > columns. Also the worksheet cell query on these lines will not open. &g...

Button caption texts disappears after changing to Theme Windows XP
Hi, I have a simple dialog just for inputting text. This dialog has a input field, and OK Cancel button. In windows classic theme, everything is OK, but if the windows theme is changed to Windows XP, the texts on OK and Cancel buttons just disappear. The dialog's title is still there, BTW. What could be the reason? Thanks. To help your analysis, I give my codes here: #include "WhiteboardTextEntryDialog.h" #include "ComponentsID.h" #include "Data/ResourcesManager.h" BEGIN_MESSAGE_MAP(WhiteboardTextEntryDialog, SkinDialog) ON_BN_CLICKED( IDOK, onOKButto...

lowest Digit Cell...
hello what's the formula to use when i have 20 cells with digits & i want to get the lowest digit on those 20 cells, in a different cell ? --- Message posted from http://www.ExcelForum.com/ i ment like If A1 is 8.02 A2 is 8.24 A3 is 9.79 A4 is 7.09 A5 is 7.15 & Cell C1 suppose to Show 7.09 --- Message posted from http://www.ExcelForum.com/ One way: C1: =MIN(A1:A5) In article <Pivotrend.ysouy@excelforum-nospam.com>, Pivotrend <Pivotrend.ysouy@excelforum-nospam.com> wrote: > i ment like If > > A1 is 8.02 > A2 is 8.24 > A3 is 9.79 > ...

What is the box character in a text field and how do I replace it?
I have a text\memo filed that has a box charter(s) in it. I do not remember if it is a carriage return or whatever. Whats is the character and how do I replace it? Thanks Dave On Tue, 27 Apr 2010 06:46:01 -0700, Dave <Dave@discussions.microsoft.com> wrote: >I have a text\memo filed that has a box charter(s) in it. I do not remember >if it is a carriage return or whatever. Whats is the character and how do I >replace it? > >Thanks > >Dave You'll need to find out what it is. My guess is that it's a linefeed Chr(10) - it wouldn...

How do I compare cells in a column
I am inputing a large column of names and numbers and would like to insure I don't repeat any. I'm not sure how the Vlookup works I continually receive errors and the If command only works with 2 cells. Thank you in advance for any help!! One way .. Suppose the list to be entered is in col F Select col F Click Data > Validation Make the settings: Choose under "Allow:" : Custom Source: =COUNTIF($F$1:F1,F1)<=1 Click OK -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>yahoo<dot>com ---- "Jim K" <Jim K@discus...

How do I find the minimum NONBLANK value in a sequence of cells?
How do I find the minimum NONBLANK value in a sequence of cells? codeslinger, =MIN(A1:A10), Min will ignore blank cells if you have a number in one cell -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "codeslinger" <codeslinger@discussions.microsoft.com> wrote in message news:69AD1DAE-BC84-47BF-8A0F-F56D2EAFD316@microsoft.com... > How do I find the minimum NONBLANK value in a sequence of cells? =min(a1:a10) =m...

multiplying numerous cells
How do I multiply a range of cells such as A1-G6 by a number such as .105 and get an individual answer in each cell. Put .105 in a cell. Click on it and then Copy it. Select all the cells for which you want to multiply. Go to Edit | Paste Special, Multiply. tj This method will replace the origianl values. "Becky" wrote: > How do I multiply a range of cells such as A1-G6 by a number such as .105 and > get an individual answer in each cell. Becky Wrote: > How do I multiply a range of cells such as A1-G6 by a number such as > .105 and > get an individual an...

change date formatting
I'm using mail merge in publisher and accessing data in access. However the data in access in showing one date format (english NZ) my regional settings are showing as (english NZ) but when the data is mailmerged into publisher it changes to a US format 10/22/05 instead of 22/10/05. I can't figure out why. If I do the same mailmerge in Office it keeps the correct format which leads me to believe its a setting in publisher but I can't seem to figure it out. HELP PLEASE!!! If you have the dates already in place in Access, changing the field to text might be the solution. Don...

Default email text size?
Outlook & Word 2003. I have Word set as my email editor. When I click for a new email it starts with a default font sized of 10. How do I change to a larger default? Word is set to default to size 12 and does this when it is started independently but when it comes up for emails it is 10. Thanks in advance... Bob Bob, Just open a new e-mail (blank) and click on Tools>Options>General>E-mail Options>Personal Stationery. Under the heading "New E-mail Messages" click Font. Then make your selections as to size, color, font,etc. Then just click OK to back your...