Custom Date Format #2

When working with data from a spreadsheet which originated from a much 
earlier version of Excel (Excel95, I believe), which I'm now trying to use 
with Excel 2003.  I'm having trouble with the formatting of the date being 
interpreted as a formula and forcing an equals sign "=" to appear next to the 
data.  

When the file is first opened, the original data appears correctly.  
However, after editing the data in the field and pressing the 'enter' key, 
the date displayed on the sheet shows "00-Jan-00".  When I use the arrow keys 
to go back up to that particular cell....it shows an equals sign followed by 
the correct date I put in...which seems to force Excel into thinking that 
it's a formula rather than a date.  Any attempts to delete the equals sign 
does not take-hold...it reappears afterwards.

When I right-click on the cell to look at the formatting of the cell...it 
shows it as being a custom date format.  Even if I change it from a custom 
date format to one of the standard date formats....the equals sign still 
appears.  Attempts to change the formatting to a standard TEXT, rather than a 
date format....still won't remove the equals sign....and still causes the 
sheet to show "00-Jan-00".

I can't seem to make ANY changes to this document without it changing the 
data in some of these date-formatted fields to display "00-Jan-00".  Is there 
some way to get the data properly showing again?  

-- 
RW Thomas
"Learn from the mistakes of others, because you won''t live long enough to 
make all of them yourself!"
0
rw_thomas (2)
6/29/2005 3:45:05 PM
excel 39879 articles. 2 followers. Follow

3 Replies
866 Views

Similar Articles

[PageSpeed] 1

On Wed, 29 Jun 2005 08:45:05 -0700, RW_Thomas
<rw_thomas@discussions.microsoft.com> wrote:

>When working with data from a spreadsheet which originated from a much 
>earlier version of Excel (Excel95, I believe), which I'm now trying to use 
>with Excel 2003.  I'm having trouble with the formatting of the date being 
>interpreted as a formula and forcing an equals sign "=" to appear next to the 
>data.  
>
>When the file is first opened, the original data appears correctly.  
>However, after editing the data in the field and pressing the 'enter' key, 
>the date displayed on the sheet shows "00-Jan-00".  When I use the arrow keys 
>to go back up to that particular cell....it shows an equals sign followed by 
>the correct date I put in...which seems to force Excel into thinking that 
>it's a formula rather than a date.  Any attempts to delete the equals sign 
>does not take-hold...it reappears afterwards.
>
>When I right-click on the cell to look at the formatting of the cell...it 
>shows it as being a custom date format.  Even if I change it from a custom 
>date format to one of the standard date formats....the equals sign still 
>appears.  Attempts to change the formatting to a standard TEXT, rather than a 
>date format....still won't remove the equals sign....and still causes the 
>sheet to show "00-Jan-00".
>
>I can't seem to make ANY changes to this document without it changing the 
>data in some of these date-formatted fields to display "00-Jan-00".  Is there 
>some way to get the data properly showing again?  

Tools/Options/Transition and uncheck the Sheet options, if they are selected


--ron
0
ronrosenfeld (3122)
6/29/2005 7:07:09 PM
This worked!  Thank you.

Are there any other caveats like that which transition over from earlier 
versions of Excel what I should be aware of?  Basically, I'm looking for 
anything else to watch out for that I might negatively inherit because of 
this legacy document from an earlier version of Excel.

Thank you again!
-- 
RW Thomas
"Learn from the mistakes of others, because you won''''t live long enough to 
make all of them yourself!"


"Ron Rosenfeld" wrote:

> On Wed, 29 Jun 2005 08:45:05 -0700, RW_Thomas
> <rw_thomas@discussions.microsoft.com> wrote:
> 
> >When working with data from a spreadsheet which originated from a much 
> >earlier version of Excel (Excel95, I believe), which I'm now trying to use 
> >with Excel 2003.  I'm having trouble with the formatting of the date being 
> >interpreted as a formula and forcing an equals sign "=" to appear next to the 
> >data.  
> >
> >When the file is first opened, the original data appears correctly.  
> >However, after editing the data in the field and pressing the 'enter' key, 
> >the date displayed on the sheet shows "00-Jan-00".  When I use the arrow keys 
> >to go back up to that particular cell....it shows an equals sign followed by 
> >the correct date I put in...which seems to force Excel into thinking that 
> >it's a formula rather than a date.  Any attempts to delete the equals sign 
> >does not take-hold...it reappears afterwards.
> >
> >When I right-click on the cell to look at the formatting of the cell...it 
> >shows it as being a custom date format.  Even if I change it from a custom 
> >date format to one of the standard date formats....the equals sign still 
> >appears.  Attempts to change the formatting to a standard TEXT, rather than a 
> >date format....still won't remove the equals sign....and still causes the 
> >sheet to show "00-Jan-00".
> >
> >I can't seem to make ANY changes to this document without it changing the 
> >data in some of these date-formatted fields to display "00-Jan-00".  Is there 
> >some way to get the data properly showing again?  
> 
> Tools/Options/Transition and uncheck the Sheet options, if they are selected
> 
> 
> --ron
> 
0
rw_thomas (2)
6/29/2005 7:36:05 PM
On Wed, 29 Jun 2005 12:36:05 -0700, RW_Thomas
<rw_thomas@discussions.microsoft.com> wrote:

>This worked!  Thank you.
>
>Are there any other caveats like that which transition over from earlier 
>versions of Excel what I should be aware of?  Basically, I'm looking for 
>anything else to watch out for that I might negatively inherit because of 
>this legacy document from an earlier version of Excel.

I don't know.  Actually, in this case, the "transition" options are for
compatibility with Lotus 123; not for older versions of Excel.
--ron
0
ronrosenfeld (3122)
6/29/2005 11:07:30 PM
Reply:

Similar Artilces:

Outlook express will not open #2
When I go to open Outlook Express, a window pop-ups saying: Outlook Express could not be started because MSOE.DLL could not be loaded. I checked to see if the file was still on my computer and it is. Please help! http://insideoe.tomsterdam.com is good for Outlook Express - this group supports Outlook from Microsoft Office. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no ...

Formatting Excel Worksheet
Please help. I have a spreadsheet which has various negative numbers entered as follows: e.g. 5.25- 0.32- 1,502.33- etc etc Is there any way to format it or a formula which will change the above numbers so that it is a negtive number e.g. -5.25, -0.32, - 1,502.33 etc etc I have tried all the usual ways to format it but the numbers still remain as 5.25- etc etc this should do it Sub fixneg() For Each c In Selection If Right(c, 1) = "-" Then c.Value = "-" & Left(c, Len(c) - 1) Next c End Sub -- Don Guillett SalesAid Software donaldb@281.com "KI" <KI@dis...

Updating the sharing of information #2
Background Information Currently I am creating a collection of web pages all linked with one another via hyperlinks. Looking at the information pages they look like web pages except they are not because they are not designed to be connected to the internet. The whole purpose of the collection of interlinked web pages is to present an collection informaton for all to access. Question What is the best method to update data from one page to another page. For example a user shuld be able to enter in text on a web pages (via Publsiher)about an upcoming event and it should appear on anoth...

master pages #2
I have a two-sided Master Page in a booklet publication that I am using for the cover page. In Master Page view, both sides of the page appear just fine. But when I exit Master Page view, the publisher page only shows up as one page. I am not using a Web publication, and I have selected two-page master when I created the page, and I have applied the master page to the publication. Why does the two-sided master page only show up as one page in the published pages? Hi - Try adding two pages. You must have four pages for 2 facing pages to "work." The front "cover" sh...

Advanced Filter Causes Crash #2
I tried both of the ideas that were offered and got great results.. thanks so much -- flipnell ----------------------------------------------------------------------- flipnello's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1491 View this thread: http://www.excelforum.com/showthread.php?threadid=26552 Great! Thanks for letting us know. flipnello wrote: > I tried both of the ideas that were offered and got great results... > thanks so much! > > -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

CRM 1.2 on a new domain
Hi, I had a network desaster a few days ago and the whole new domain was created. I had a CRM1.2 server on my old domain which i need to use. Can anyone help me to iether add the old CRM to the new domain or somehow export all the data and add to a new CRM in the new domain? you will need to re-deploy the CRM databases. Download the kit here: http://www.microsoft.com/downloads/details.aspx?FamilyID=bfced393-61db-49af-9a50-4a90b311fa7d&DisplayLang=en "mohammad" <mohammad@discussions.microsoft.com> wrote in message news:EA61B611-8E66-4F66-81F1-B4E39E342D19@microsoft....

Money 2005 Statement Date
I stopped downloading statements from my bank years ago, but on the home page next to an account name I still have the date in 1999 that I last downloaded the statement. Can anyone assist with how I can clear/remove this date from my home page? Thanks. In microsoft.public.money, spook wrote: >I stopped downloading statements from my bank years ago, but on the home page >next to an account name I still have the date in 1999 that I last downloaded >the statement. > >Can anyone assist with how I can clear/remove this date from my home page? When you delete a...

Formatting a Row Question
Is it possible to conditionally format an entire row based on input into one cell in that row, or am I "rowing" upstream? All help on this appreciated, Bryan Yes, perhaps a simple example to illustrate .. Select row1 (click on the row header) Click Format >Conditional Formatting Under Condition 1, make the settings: Formula Is | =$B1=1 Click Format button > Patterns tab > gray > OK Click OK at the main dialog Now key in a "1" into B1, press Enter Row1 will be coloured gray Clear B1, the colour disappears (Note that the $ sign in the formula is import...

Relationship difference between CRM 3.0 and 4.0, custom enties
Hi, I am trying to create a workflow with two custom enties created in CRM 3.0. When I try to fill in the workflow create form it is all greyed out, is this because there may not be a relationship between the two? ...

Drop down box with country list #2
Thanks Bob, however what I want is a dropdown which we can find in the web site applications (e.g. airways ticketing) where we can use choose a country using the drop down with a complete list of world's countries. In Data Validation method, I have to re-type all the possible country's names. John. Bob Phillips Wrote: > Enter the countries in a range (say column M) and then add a Data > Validation > (DataValidation) and select the List option with a formula of say > =M1:M100 > or however many countries > > -- > HTH > > Bob Phillips > > "jo...

Help with Advanced Filter criteria for customized email inbox view
Using MSOutlook2003 I customized my Outlook Inbox view with an advanced filter. The criteria were Field Condition Value -------- ----------- ------ Due By does not exist Due By on or before Today() The result was that I see all of my email messages that either don't have a reminder set (flag and due date) or have a reminder set for any time after 12:00:00 AM of the current day. This worked very well. Everything that is due on a future day was hidden from view so I didn't have to look at it. I then decided that I also wan...

Queue Custom Views
This is reaching but is it possible to change the main Grid of the Queue? It only displays Title, Entered Queue, and Type. We are trying to utlize a workflow that kicks out a service activity to various queues when an case is created and need to display more columns describing the activity. Thanks! Did you ever get this figured out as I need to do the same? -- Dennis "MDV1457" wrote: > This is reaching but is it possible to change the main Grid of the Queue? It > only displays Title, Entered Queue, and Type. We are trying to utlize a > workflow that kicks out ...

format date
I am working on a spreadsheet that someone else created. The dates are set as e.g. 13.03.09 - I want this to read 13/03/09 I have tried formatting cells to date dd/mm/yy and clicking ok but it doesn't alter anything. Apart from going in and altering each cell individually nothing else seems to work. Please help -- Kaz Karray wrote: > I am working on a spreadsheet that someone else created. The dates are set > as e.g. 13.03.09 - I want this to read 13/03/09 I have tried formatting cells > to date dd/mm/yy and clicking ok but it doesn't alter anything. Apart from &...

Error -2147217873 #2
V 1.3 We are getting the following error on one of our locations: Error -2147217873: Cannot insert duplicate key row in object 'Configuration' with unique index 'IX_Configuration_StoreID'. Any, any thoughts? -- Jocelyn This is a multi-part message in MIME format. ------=_NextPart_000_01DB_01C6FC37.C59895B0 Content-Type: text/plain; charset="Utf-8" Content-Transfer-Encoding: quoted-printable Jocelyn, Did you try re-indexing and running a check like I suggested last week? --=20 Jeff=20 Check Point Software =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3...

adding 1 day to a date
I would like to be able to display a date which will be one day more than what i type in, but if the date i type in is a friday i would like it to display Mondays date Thanks I assume you'd also want a weekend date to step forward to the following Monday. Add the following function to a standard module: Public Function NextWorkday(dtmDate As Date) Dim dtmNextDay As Date dtmNextDay = dtmDate + 1 Do While Weekday(dtmNextDay, vbMonday) > 5 dtmNextDay = dtmNextDay + 1 Loop NextWorkday = dtmNextDay End Function In the AfterUpdate event proc...

what is correct way to assign a custom event handler?
I have a (test) form with 3 panels and a button on each panel. I want to assign a single event handler to the 3 buttons using an anonymous method (just experimenting here) - this based on an example in a prior post (of mine). The compiler is complaining as noted below. What is the correct way to do this? using... ... namespace CsharpProj2008 { public partial class Form1 : Form { string[] s1 = new string[] { "test1", "test2", "test3 }; public frmMultiBtns() { InitializeComponent(); } //>>---complaining in this block bu...

How can I put 2 appointments on the same calender day
Hi this is probably not an Excel related question?? -- Regards Frank Kabel Frankfurt, Germany "bray737" <bray737@discussions.microsoft.com> schrieb im Newsbeitrag news:EE751FE0-57D2-4077-BE80-7FB578CD8FC2@microsoft.com... > ...

Interest Only Mortgage #2
How do I add an interest only mortgage as new account? The HELP within Money Plus Deluxe says to open a "Long Term Loan" but that option doesn't exist. http://support.microsoft.com/kb/893719/en-us "kenclem" <kenclem@discussions.microsoft.com> wrote in message news:59700D74-0E2F-4119-966B-C9FF77EFDB99@microsoft.com... > How do I add an interest only mortgage as new account? The HELP within > Money Plus Deluxe says to open a "Long Term Loan" but that option doesn't > exist. ...

Forecast Function #2
How can I use the forecast function on columns that are not adjacent to each other, or alternately, is there another function I can use for this. Otherwise, I have to type in the whole regression line equation thusly, =AVERAGE(L3,AB3,AR3)+((((3*((L3*1998)+(AB3*2001)+(AR3*2002)))-((L3+AB3+AR3)*(1998+2001+2002))))/((3*((1998^2)+(2001^2)+(2002^2)))-((1998+2001+2002)^2))*(2003-AVERAGE(1998,2001,2002))) I'm forecasting 2003 data from 1998,2001, and 2002 data. Thanks, X ...

Date Format 12-10-09
Dear experts, I've a worksheet of which column D & E are for dates. However, in these 2 columns, the users input the dates in different format. I want to change the dates into one format only such as: 21-Feb-09 (dd-mmm-yy). However, I highlighed the cells and changed the format to "dd-mmm-yy", the original dates do not change and until I re-type the dates again. It's a headache exercise if I need to re-type the dates again, please advise any quick method to achieve my goal. Thanks in advance Place the cursor in the date cells and press F2 and give ent...

Conditional Formatting Help Please 01-25-08
I have a datasheet form where I had several conditions depending on a couple of fields one of the fields is numeric. I have been asked to add another number field and a condition of changing back color . When opening form the last condition is being applied even though the field associated with this condition is blank. How can I fix this? Thank you for any help you can provide. You're not the only one who's totally confused! You really need to give us a few more specifics if anyone is to help you. Names of the fields involved, what you have in the Conditional Formatting of w...

sending mail from 2 email addresses
I have successfully got our exchange 2003 system to pull in mail for domainA.com and domainB.com Users have email addresses at both domains. Can Outlook2003 be configured to send mail from "EITHER" of the domains or does the Primary address in the recipient policy always rule. TIA Gary The primary address rules. So much so that some people created a 'choosefrom' application for this situation: http://www.ivasoft.biz/choosefrom.shtml Alternatively, you could create a second mailbox for the second domain and have them use OWA for the second client (which would need ...

Concatenate Date
Good Afternoon! I have a table that has two fields representing the date as follows: ReportingMonth (numeric 1-12) FiscalYear (20072008 for FY 2008) I would like to concatenate this, which seems simple enough, but I would like to somehow translate this into the calendar date in a query (month and year). Our fiscal year runs from July to June, so if it's months 7-12, then it would be 2007, and 1-6 year 2008. I have information over multiple fiscal years. Any help would be appreciated. Thanks! DateSerial( 2007 - Int( theMonth <= 6 ), theMonth, 1 ) which is based on the ...

Outlook 2003
I journal my contacts, so can see all email exchanges from the Activity tab. However, I just want to retrieve the date of the last activity and either append it to the Notes section, or use it to populate a custom field. Can this be done in Outlook directly? (I can export the contacts/emails to Access and run queries there, but wanted to know if there is a local solution) Thanks! you can always look for the date (after using Activates tab) and type it in the field or it should be doable using VBA if you want to automate it. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outl...

serious bug wrap text + conditional format
Hi, I have a serious bug to report in an excel file Changing the format to wrap - in one cell only - saving - open again - BANG. Excel "try to repair the file... and I am left naked ! - no more format - no more vba macros - no more formula - ... nothing Aaaaahhhrghhhh ! Two full days of work... and I have the autosave on... How can I post the file to give it as a (BAD) exemple ? and possibly get a fix ? File description: - one column has a data validation pull down menu taken from a named list + a conditional format to set the cell in red if the value chosen is out of the list - the...