Date shows as 01/01/00

We have a spreadsheet that we want to enter a date in a cell. I have changed 
the format to Date 03/01/01 format but no matter what I do, any date is 
converted to 01/01/00. I have tried inserting a column, copying to another 
spreadsheet, etc. I know this is probably simple, but it's driving me crazy. 
Help please.
0
Kmagg (3)
9/23/2005 7:12:03 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
818 Views

Similar Articles

[PageSpeed] 25

In an un-used cell enter:

=TODAY()

if this is not the correct date, then touch CNTRL-ALT-F9 on the keyboard

post what happens.
-- 
Gary''s Student


"Kmagg" wrote:

> We have a spreadsheet that we want to enter a date in a cell. I have changed 
> the format to Date 03/01/01 format but no matter what I do, any date is 
> converted to 01/01/00. I have tried inserting a column, copying to another 
> spreadsheet, etc. I know this is probably simple, but it's driving me crazy. 
> Help please.
0
GarysStudent (1572)
9/23/2005 9:43:03 PM
Try tools|options|transition tab|Uncheck Transition Formula Entry.

With this checked, excel behaves like Lotus 123 and sees a division:

But I think your post is wrong <vbg>.  

You see 01/01/00 when you type in a date of 01/03/01.

1 divided by 3 divided by 1
is equal to:  0.333333333333333
Or 1/3 of day one in excel speak.  
And day one is January 1, 1900.

(You'd see Jan 1, 1900 8:00 AM if you formatted it differently.)

While you're in that tools|Options|transition tab, you may want to turn off all
those "helpful" Lotus 123 settings--especially if you haven't use (or don't use)
123 anymore.

Kmagg wrote:
> 
> We have a spreadsheet that we want to enter a date in a cell. I have changed
> the format to Date 03/01/01 format but no matter what I do, any date is
> converted to 01/01/00. I have tried inserting a column, copying to another
> spreadsheet, etc. I know this is probably simple, but it's driving me crazy.
> Help please.

-- 

Dave Peterson
0
petersod (12005)
9/23/2005 10:51:00 PM
Reply:

Similar Artilces:

Show format in formula bar
Hello: On some spreadsheets I have #'s in the billions added together in cells. Sometimes it is difficult to verify the number is 10,000,000,000 because it shows up as 10000000000. My eyes get crossed counting the zeros. I'd love if the formula bar would show 10,000,000,000; however, can't seem to find a way to do this. Is this possible? No, you can't change the number formatting in the formula bar. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Native" <native_texan_03@yahoo.com> wrote in message n...

Outlook 2007 Address Book 01-21-10
I've just installed Outlook 2007 and it's imported my address book from Outlook 2003 as a single list of contacts/addresses with 150 names! This is too unweildy of course, so I want to divide these up into about 6 or 8 separate groups according to the contact nature. The help advice online does give methods of creating sub-folders but I can't get either of them to work - would appreciate any workarounds please. -- Chris 1-9 (UK) "Chris 1-9" <Chris19@discussions.microsoft.com> wrote in message news:50F25761-01EC-4EE5-A440-980E48511A8F@microsof...

Best Practices shows 114 GB Database Size limit
I just ran a best practices scan against my Exchange 2003 SP2 Server and it shows that the Mailbox limit is 114 GB. I though the limit was going to be 75 GB in SP2? The value data in the Database Size Limit in GB registry key is 72 hex. DO I have it configured wrong? SHould it read 114 GB in the best practices scan? Any help would be appreciated. Thanks -- jgarcia 72 Hex is 114 decimal...you need to set it correctly in your registry. "mrjules" <mrjules@discussions.microsoft.com> wrote in message news:8BD7A4CB-DFA0-4485-844F-3BE6A3A56ACE@microsoft.com... >I ju...

Subquery to find consecutive dates
Howdy Folks, I have a table that contains a series of asbestos sampling dates at several different locations. A simplified version of the structure is as shown below: [Location] [Sampler] - Company that performed the sampling [Sample Date] [Result] - Numerical Result [Detection Flag] - Yes or No When the detection flag is "Y" for a given day, I want to know if the next day also has a detection. If not, I want to ignore that detection. If yes, I want the first day to get flagged with a "1" and the subsequent day to be flagged with a "2". This gets trickier when ...

SOP Actual Ship Date
In SOP setup, we do not use a Separate Fulfillment process. So, I'm assuming the Actual Ship date defaults from either the system date or the order date on the date the order is created (because it's also fulfilled on that date). Is there any way that we can have the Actual Ship date default to the Invoice date at the time the order is transferred to an invoice? We're trying to get a SmartList that shows (by item) Order Date AND Actual Ship Date/Invoice Date. Is this possible some other way without SmartList Builder? Elaine, 1. Is there any way that we can h...

Secinds from 1980-01-01
I have a cell that shows a date shown in seconds from 1980-01-01 and i want to convert this....how could i do this? thanks nails --- Message posted from http://www.ExcelForum.com/ Hi nails! Use: =DATE(1980,1,1)+A1/(24*60*60) Format as (eg) dd-mmm-yyyy hh:mm:ss Or: ="1980-01-01"+A1/(24*60*60) -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au It is imperative that the patches provided by Microsoft in its April Security Release be applied to Systems as soon as possible. It is believed that the likelihood of a worm being released SOON that exp...

How do I do a slide show in Publisher?
I need to do a slide show from Publisher...how? No tools found to help! Publisher doesn't do slide shows. Try PowerPoint... -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com http://officebeta.iponet.net/en-us/publisher/FX100649111033.aspx "Susan" <Susan@discussions.microsoft.com> wrote in message news:BB3B5FA1-0DB9-4E03-8B24-234FC1B50882@microsoft.com... >I need to do a slide show from Publisher...how? No tools found to help! Susan wrote: > I need to do a slide show from Publisher > ...how? No tools ...

Help!!Getting audio files to play continuously during slide show?!
I have Powerpoint 2002, and i am having a hard time getting the music file to play continuously during the slide show. it plays on the first slide (which is the only slide that has the little speaker picture on it), but then it doesn't transition to the next slide until after the song is done, when i want the song to play while the slides are transitioning. HELP please! Mega, Have a look here and see if it helps, there are tuturials in video demonstrating the process. http://www.pptfaq.com/FAQ00047.htm -- Luc Sanders MVP - PowerPoint "MegaB86" <MegaB86...

how do i count mails with time and date
i have a helpdesk , i would like to know the tracking all mail , as like time and date on responded "Bhanu C" <Bhanu C@discussions.microsoft.com> wrote in message news:4F0F08B1-3302-43F9-B2F8-5FE1BEC99A9B@microsoft.com... >i have a helpdesk , i would like to know the tracking all mail , as like >time > and date on responded Why not just look at the InfoBar? -- Brian Tillman [MVP-Outlook] see http://www.slipstick.com/exs/customfields.htm - specifically the replied time section. If needed, you can copy rows to excel and compare dates etc. -- Diane Poremsky...

Formatting date fields after export
I am experiencing problems with my exported date fields into Excel from other applications. The data formats to "yyyy-mm-dd" and cannot be modified unless I double-click on each field. Has anyone else experienced this problem? And what solutions would you suggest? It is probably seen as text, select the imported dates, do data>text to columns, click next twice, under column data format select date and YMD click finish Regards, Peo Sjoblom "Raymond" wrote: > I am experiencing problems with my exported date fields into Excel from other > applications. The d...

Positions showing up instead of transactions
Greetings, I am currently using Microsoft Money Plus Deluxe and just recently (a week or two) I am experiencing a problem with my Vanguard transactions not working properly. I used to get Vanguard mutual fund and brokerage services transactions (buy, sell, etc.), but now I'm only receiving position adjustments (add x amount of shares instead of actual transaction on specific dates). I've been using Microsoft Money since 2001 so I know it can get wonky at times. I'm not sure if this is just another temporary Money problem though or whether Vanguard has changed how they handle elec...

Combined date time cell to separate date & time components #3
From file dump have combined date time cells eg 14/04/03 14:20 (value 37725.59722). Want to perform time analysis so need to split to 2 separate cells; eg. "14/04/2003" (value 37725) & "14:20" (value 0.59722). Is there a fnc to do this? (Currently convert cell to value, then fncs trunc & cell less int(cell) then refmt to date dd/mm/yy & time hh:mm respectively) Please look for the previous answers you've gotten, including at least 2 to your identical post yesterday. In article <F0A5B5BA-2E80-4875-A042-D3514FD4E228@microsoft.com>, "Ma...

customizations in web client not showing up in outlook client
I setup a sample customization on the opportunities screen in v1.0. It shows up fine when i pull up an opportunity in the web client. However, if I pull up Opportunities in the outlook sales client, i don't seem my button listed. Is there something I'm missing here? Hi Mike, did you follow the instructions in the CRM Outlook Customization SDK? Remember that the Outlook client uses a different customization file than the web client does. Mike "Mike Liddekee" <mliddekee@humco.com> wrote in message news:Oeuwys72DHA.2136@TK2MSFTNGP12.phx.gbl... > I setup a sam...

Date format and ...
Hi all, please help me with: 1) I get the date format from Windows Control Panel (locale info) with 'GetLocaleInfo()', these formats are: dd/MM/yyyy dddd, dd' de 'MMMM' de 'yyyy But I have mi date in a COleDateTime object and COleDateTime::Format() does not work with these date formats, ¿How can I format my COleDateTime with dd/MM/yyyy and dddd, dd' de 'MMMM' de 'yyyy date formats? 2) Also, I have a CDateTimeCtrl and CDateTimeCtrl::SetFormat() works fine with dd/MM/yyyy and dddd, dd' de 'MMMM' de 'yyyy, but it does not work...

Date query 08-03-07
Hi I'm trying to write a query that selects records based on a date range and Uses the Date of Birth if the Date entered is blank (null) or uses the date entered if that date is filled in. My query works fine for the first condition where I say if "date entered is null" and DOB between the 2 dates. My problem comes in when I put in the second line in the criteria and say if the date entered is between the 2 dates. Any help would be appreciated. Thanks Jeff ...

how to count if the value falls between a date range
example 1/1/2005 1/2/2005 = 2 2/1/2005 2/2/2005 = 2 I am trying to get the ount if the value is between 1/12005 and 1/3/2005 and similarly 2/1/2005 and 2/3/2005 =countif(a1:a10,">="&date(2005,1,1)) - countif(a1:a10,">"&date(2005,1,3)) (I'm not sure what between means--include the end dates or not???) joe wrote: > > example > 1/1/2005 > 1/2/2005 = 2 > 2/1/2005 > 2/2/2005 = 2 > > I am trying to get the ount if the value is between 1/12005 and 1/3/2005 and > similarly 2/1/2005 and 2/3/2005 -- Dave Peterson Dave, ...

Export don't show all Textboxes
I have a report which calculates on an condition some textboxes: Private Sub Detail_Print() If Me.Text62 > 30 Then 'Me.Text62.Visible = False Me.Textover30.Visible = True Me.Text15to30.Visible = False Me.Text2to15.Visible = False Me.Text0to2.Visible = False Me.Textfuture.Visible = False ElseIf Me.Text62 <= 30 And Me.Text62 >= 15 Then 'Me.Text62.Visible = False Me.Text15to30.Visible = True Me.Textover30.Visible = False Me.Text2to15.Visible = False Me.Text0to2.Visible = False Me.Textfuture.Visible = False etc........ End If As...

Can a variable (date) be used in a SUMIF formula?
I hoping that you can help me. Below you will find an example of a spreadsheet that I'm trying to to perform a sumif formula with dates. In the constant row I use the formula of =SUMIF($B$7:$B$11,">12/12/2002",$C$7:$C$11) and receive the value of 1,695.00. In the variable row I use the formula of =SUMIF($B$7:$B$11,">c3",$C$7:$C$11). I'm trying to acutally use the row of dates instead of having to put in the actual date each time but when I use the field of C3 for the date it returns the value of 0. A B C D ...

Parameter Query For Selected Dates or All Dates
Hi Folks - I'm having a senior moment. I have a query with a date field. I want to use Between [Start Date] and [End Date] to choose a date range, but if the user leaves the parameters blank, I want all records returned. I was able to do this when I passed a form control to the query, but I can't figure out how to do it within the criteria row of the query. Any help would be appreciated. Thanks. -- Michael On Wed, 12 Dec 2007 17:02:00 -0500, Michael wrote: > Hi Folks - I'm having a senior moment. I have a query with a date field. I > want to use Between [Start Da...

Date Values
I currently have a cell that shows week commencing, ie Mondays date only for any particular week, I currently update this cell every week. I have been looking, unsuccessfully, for a function to automate this process, any ideas. Thanks Alec =TODAY()-WEEKDAY(TODAY(),2)+8 will show next Monday even on a Monday -- Regards, Peo Sjoblom "Alec" <anonymous@discussions.microsoft.com> wrote in message news:003201c39e4d$9d0f4580$a601280a@phx.gbl... > I currently have a cell that shows week commencing, ie > Mondays date only for any particular week, I currently > upd...

Format Date #2
Is there any way that when I enter a Date in say Cell A1, Cell A2 would be able to reflect the Day? Hi try =A1 and format with the custom format DDDD or use the formula =TEXT(A1,"dddd") "Alicia" wrote: > Is there any way that when I enter a Date in say Cell A1, Cell A2 would be > able to reflect the Day? > > Thanks, But currently i even have problem format date to watever format i want, like dd-mmm-yy or dd/mm/yy... i know it is possible, there must be something in my excel that is wrong. Any clue on wat might be wrong. Even when i present calcul...

Conditional Formula based on previous date + 30
I have a spread sheet that caluclates when proposals expire. Date Sent Follow Up date January 13, 2010 February 12, 2010 What I am looking for is for the Follow up date to turn RED when the date is expired (over the date listed). Just use CF with a formula of =B2>TODAY() -- HTH Bob "Chris" <Chris@discussions.microsoft.com> wrote in message news:66DE65F1-F041-434C-86A7-B13635C6914F@microsoft.com... >I have a spread sheet that calu...

Formula result shows as zero Excel 2003
Formula as shown =IF(C12<>"",+C11-C12,"") appears in D12. This is the same formula as rows above but rows above show correct result whereas D12 and subsequent rows show only zero. If I do an F2 and F9, the correct result shows in the Formula Editing bar so formula is working correctly. I have tried copying both formula and cell formatting from previous rows which do display their result correctly but still doesn't fix the problem. Any assistance would be much appreciated Hi maybe automatic calculation is disabled. Check 'Tools - Options - Calculate...

Format cells with dates
Is there a way to format cells so that dates would change when the lead date is changed. for example, when I input monday's date, tue, wed, thur, etc will follow suit. Pat, Assuming the first date is in A1 B1: =A1+1 C1: =B 1+1 etc. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Pat" <anonymous@discussions.microsoft.com> wrote in message news:115681B0-348E-447E-BB1F-F8347CFDB19B@microsoft.com... > Is there a way to format cells so that dates would change when the lead date is...

CRM 3.0/4.0: Views to show record that is created X Days Ago
I have a simple requirement to create a View with condition that the record is created x days ago, for example the record that is created 3 days ago. The only operator that is available for datetime (e.g. createdon) is only Last X Days, which if i show Last 3 Days, will show the records that were created today, yesterday, and 2 days ago. Is there any operator or any way to show the record that were created 3 days ago? I try to put condition "createdon Last 3 Days" and "NOT createdon Last 2 days", but there is no "NOT" operator in CRM. I try to insert new attribut...