dates get corrupted when copied to another workbook

I copy a worksheet in a workbook to a new workbook, a date of 10/13/03 becomes 10/22/99, off by 4 years and a day. A cell by cell copy has the same resuly. Never seen that before. I am using excel xp
anyone seen this before??
0
anonymous (74722)
10/28/2003 1:41:02 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
267 Views

Similar Articles

[PageSpeed] 59

One workbook was using a base year of 1900 and the other was using 1904. 
(tools|options|calculation tab|1904 date system)

One way to add those four years back is to find an empty cell, put 1462 into
that cell.  

Copy that cell.

Select your range that contains the dates.  Edit|PasteSpecial|click Add (in the
operation box).

You may have to reformat the cell as a date (mine turned to a 5 digit number). 
But it should work.

You may want to do it against a copy...just in case.

(I'm not sure which one you'll fix.  You may want to edit|pastespecial|click
subtract.)

Most windows users use 1900 as the base date.  Mac users (mostly??) use 1904 as
the base date.



jimK wrote:
> 
> I copy a worksheet in a workbook to a new workbook, a date of 10/13/03 becomes 10/22/99, off by 4 years and a day. A cell by cell copy has the same resuly. Never seen that before. I am using excel xp
> anyone seen this before??

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
10/28/2003 1:52:26 AM
Jim,

Excel supports two date systems: one uses 1-Jan-1900 as the basis of dates,
and the other uses 1-Jan-1904 as the basis of dates.  This setting is
specific to each workbook.  In each workbook, go to the Tools menu, choose
Options, then the Calculation tab. There, ensure that the "1904 date system"
setting is the same for all workbooks. In general, this setting should not
be enabled; its primary purpose is for compatibility with Macintosh systems.


-- 
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com    chip@cpearson.com

"jimK" <anonymous@discussions.microsoft.com> wrote in message
news:30FDAE99-7850-4448-BB24-E367979198ED@microsoft.com...
> I copy a worksheet in a workbook to a new workbook, a date of 10/13/03
becomes 10/22/99, off by 4 years and a day. A cell by cell copy has the same
resuly. Never seen that before. I am using excel xp
> anyone seen this before??


0
chip1 (1821)
10/28/2003 1:58:52 AM
That worked, I simply change the format to 1904 for this worksheet. Turns out the original was developed on a mac

Thanks
0
anonymous (74722)
10/28/2003 2:11:07 AM
Reply:

Similar Artilces:

Get External Data from Web
Hi I am pulling in currency exchange rate external data from a web site... no problems and storing the information on a worksheet. I have a userform with txtboxes that are linked to each of the cells. When the user form loads, the txtbox shows the currency exchange rate data imported in from the web site...agian no problems. I want to have a cmdbutton on the userform to update the data, i'm using:- ActiveWorkbook.RefreshAll but my question is the actual refresh will no take place until the userform is closed. How can i make the refresh data run whilst the userform is...

Sqldatasource parameter date
I have a slight problem resetting a date select parameter in that I can't get the records with null dates. For instance this select statement SelectCommand="SELECT * FROM [MyTable] WHERE ([DueDate] < @DueDateParm)" <asp:SessionParameter Name="DueDateParm" SessionField="DueDateParm" DefaultValue="" ConvertEmptyStringToNull="true" DbType="DateTime" /> If I set DueDateParm to a date it works fine I get all of the recors less than that date. But I can't figure out how to reset it so every record comes ...

How do I get my new email previewed on my desktop? #2
Hi- I'm very new here and could use some help. I've accidentally deleted my desktop email that appears on the sidebar. It shows new mail before I open Outlook. How can I sort this out? I would appreciate any anwers as I am frustrated and lost. Many thanks!!! Conner are you using vista's side bar with a mail gadget? right click on the side bar, choose add gadget. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipstick.com/outlook/ol2007/ Outlook Ti...

creating sorted copy of table on second sheet
Hello all, table on sheet 1 consists of (say) a row with names and a row with room numbers and is sorted by name. (First row is header and not included in sorting.) I want to create a copy of this table on a second sheet containing the same data sorted by room number which automatically re-sorts when the data in the first table is changed. I could think of a matrix function but could not find any. Any idea? -- email me: change "nospam" to "w.hennings" Dipl.-Ing.(=M.Sc.Eng.) Wilfried Hennings c./o. Forschungszentrum (Research Center) Juelich GmbH, MUT <http://www.fz-juel...

Journal Start Date
I "activated" (first use) Journal on Dec. 1. How can I get it to track email and other items for dates going further back...say to March. It only seems to be journalizing everything from Dec. 1. I'm using Outlook 2007 you'd need to create the items - it only knows things it tracked after it was enabled. If you need to see history with contacts, you can use the Activities page on the contact. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by e...

From an analytical's standpoing -- how to get categories right?
Okay, I'm trying to use Money for what it's supposed to be for, categorizing everything properly, and not throwing everything into the "Miscellaneous" category. But what about a number of odd things, such as: Stamps or other similar items... I can't call them Groceries, and it doesn't seem proper to call them Household, either? Tithing... giving my 10% to my church doesn't seem to have an applicable category. I'm sure there are others, but these are the two that have been bugging me as of late. Ken In microsoft.public.money, Kenneth Pardue wrote: ...

Opening Toolbars attached to workbooks
I've attached a toolbar to a workbook and I would like it to open when the workbook is opened and close when the workbook is closed. Any help greatly appreciated. DR DR, You will have to write some code... there are many samples out there for that.. here is a link to one of mine written a few years back: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnoffpro01/html/customexcelmenus.asp -- Charles www.officezealot.com "Blackcat" <Blackcat@discussions.microsoft.com> wrote in message news:CB356805-54CC-441F-B31A-A79C2D5A62AE@microsoft.com... > ...

Excel
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I created a single worksheet with a number of cells that I input values, then I duplicated it 11 times (one worksheet for each month of a year). Now I created another worksheet (again in the same workbook) and I want to create a yearly total from the same cells across previous 12 worksheets. I know it can be done (did it a number of years ago) but I can't fine the formula content necessary to perform this task. <br><br>Can anyone help? <br> Thanks One Option, assuming Sheet1-Sheet12 are...

How to create list based on data from another list
Hi, I need this for a school project. I belive it's very basic, but I can't understand what do I have to do, so if anyone could help me please. example of the list: NAME MEMBERSHIP PRICE xx yes 100 yy no 150 zz yes 100 (this list goes on) now I have to create another list which will contain (show) only data of the people who has YES in the membership column. I've searched the net for the answers and what I found is that I should choose LIST option from the DATA menu and do...

why am I getting duplicate emails?
when an incoming email is listed on my page it is listed 2 times. This started last night. "redheadl-l" <redheadl-l@discussions.microsoft.com> wrote in message news:4A369C7D-02F9-4462-BFE5-15DB48A7953C@microsoft.com... > when an incoming email is listed on my page it is listed 2 times. This > started last night. Do you have duplicate Accounts set up at Tools>accounts, if so delete one of them. -- Regards Steve. MS-MVP. MAIL. [DTS] UK. http://www.getsafeonline.org/ "redheadl-l" wrote: > when an incoming email is liste...

Populating a column in a worksheet from another worksheet based on a common field
Hey folks... I know this has had to have been done before. Basically, I have an original worksheet, and another worksheet that contains values that I want to populate a column in the original worksheet with, based on a common field. But it gets tricky, because the 2nd worksheet contains some IDs (common field values) that are NOT in worksheet 1. And I'd like to know which ones those are, preferably with them being put into a 3rd worksheet automatically... here is the original spreadsheet in a nutshell: PARCEL_ID | ELEVATION 1111 2222 3333 4444 The elevation field is empty--- it's...

Copying print setup features
Is it possible to copy the print setup features (page formats, headers, footers) between tabs on a worksheet? If you group the worksheets first. Click on the "master", then ctrl-click on subsequent, then do file|page setup, lots of these settings will carry across to the other sheets. There are a few that won't, though. vgriff wrote: > > Is it possible to copy the print setup features (page formats, headers, > footers) between tabs on a worksheet? -- Dave Peterson ec35720@msn.com ...

Opening Archive from one account to imprort into another
I am having a problem with importing an archived .pst folder from one Outlook Profile to another. The archive was created on one computer using Office 97. I no longer use that computer so copied the archived files to a disc. I now want to import thses files in to my new profile in Outlook 2000 on another computer, but every time I do it comes up with 2 error msg's "Properties for this information must be defined before use" and then "File access is denied. You do not have the permission to the file .........." Can anyone help? Did you remove the read only att...

Retrieve email from another computer
My husbands computer in office is down, he wants to retrieve his emails from home. How can I do this quickly? Tks That depends on the type of mail system they use at work (server based postoffice, or Internet mail, or what). Can you give us some more information? -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup to preserve the message thread. *** In news:0f9a01c3dfb1$12f44670$a101280a@phx.gbl, maureen.krychkowski@verizon.net wrote: > My husbands computer in office is down, he wants to > retrieve...

getting a message that auto recovery is postponed?
Message says AutoRecovery postponed for <file>. AutoRecovery box is checked in Word Options. Running Windows 7 and Word 2007. ...

Sort across multiple worksheets in a workbook
Hi, I have a workbook in Excel 2003 that has 10 worksheets each laid out identically with the same one-line header having 8 columns but varying number of rows, say R1, R2... ...R8 that is are added to daily. I expect in time the total number of rows in the 10 worksheets will exceed 65536, the limit on rows in any worksheet. Is there a way to sort all the entries in the workbook by, say, Column E in all the 10 worksheets and either dump the resulting sorted data in the existing worksheets or in new worksheets in the same/new workbook? If need be, I can abandon the labels on worksheets 2 -...

old excel file that getting kind of buggy but contains important d
I have an old excel file which is related to a continuing project which i have been updating with new data and new analysies since May 2006. It used to be .xls but i changed it to .xlsx when i got new version of office. The graphs take ages to load and i can't e-mail it even though it's not to big to be sent by the account i'm using. It's 3.6 Mb, i think the accounts i tried let me send up to 10, i've tried it on both yahoomail and gmail so it's not the account. It used to have abut 14 worksheets, but i broke it up intto two workbooks on with 3 worksheets and th...

Text Box & Date Updating
I'm creating a mail merge postcard and would like to put an expiration date in a text box. I see how I can insert a data field with the current date, but how can I make the date show 90 days out? See fellow MVP, Macropod's Date Calc 'tutorial', at: http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=249902 -- Hope this helps, Doug Robbins - Word MVP Please reply only to the newsgroups unless you wish to obtain my services on a paid professional basis. "osbornauto" <osbornauto@discussions.microsoft.com> wrote in message news:492F...

I keep getting messages(20 at a time)with no address,name,message
As mentioned in subject I have been getting numerous messages, one right after the other in Outlook Hotmail account with noa ddress, no name, no subject , and no pmfprmation in the body. It is creepy. The size I notice is consistently 199"B",,,just b not kb. Anyone know how to flag the sender as blocked??? See http://www.msoutlook.info/question/423 -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more http://www.msoutlook.info/ Real World Questions, Real Wor...

Copy/paste links
I have one row of links, ie cell D51 = '[Fin Spt.xls] Summary page'!$I$49, cell E51 = '[Fin Spt.xls]Summary page'!$J$49, cell F51 = '[Fin Spt.xls]Summary page'!$K$49, and so on. I want to copy that row, paste it in rows 52- 58, but I want the links to follow suit (49 in row 51, 50 in row 52, etc). any help? Take out the $'s before copying and pasting. The dollar signs fix the cell reference as an absolute reference. >-----Original Message----- >I have one row of links, ie cell D51 = '[Fin Spt.xls] >Summary page'!$I$49, cell E51 = '[Fin S...

How do I skip blank cells when copying over a range of cells?
I would like to copy a column of values that have blanks between them over to another column and skip the blanks. The skip blanks feature in paste special does not help this issue. If you mean that you want to copy a column of cells, but omit the blank cells (as if they don't exist), try this: 1)Autofilter the range......Data>Filter>Autofilter 2)Set the column filter to be Non-Blanks 3)Copy the range, then paste it where you want it The blank cells will be left behind. Example: A1: Amount A2: 50 A3: (blank) A4: 55 Using my suggestion, would A1:A4 would copy/paste as this A10...

"References" not under the Tools menu, how to get this?
This is re some vba scripting. I'm required to use Tools > References but this is not available on my Outlook. I searched the MSKB without luck for a solution. How does one do this, pls? [I have O2K on Win2K at office and O2K on WinXP at home.] Thanks. :oD StargateFanFromWork wrote: > This is re some vba scripting. I'm required to use Tools > > References but this is not available on my Outlook. I searched the > MSKB without luck for a solution. > > How does one do this, pls? Tools - References isn't in Outlook proper, but is in the VBE development ...

creating a formula that looks at the value of another cell and uses that value as the row value for for a referenced cell
Seems simple enough, but I have no idea how to do this. Here's an example: =IF(ROW(A3)<AO$1,'Page2'!$A?,""), where the question marks denotes the row number, the value of which, I want to get from another cell altogether. For example,say cell B1 had the value of 1000 and I wanted to use that value as my row number in the above formula so something like =IF(ROW(A3)<AO$1,'Page2'!$A(value from B1),"") = =IF(ROW(A3)<AO $1,'Page2'!$A1000,"") Thanks, K Try: =IF(ROW(A3)<AO$1,INDIRECT("Page2!$A"&B1),&...

Copying everything
hello, I have a large (26 * 750 cells) complex sheet which I want to copy to another sheet in the same workbook. I want to copy everything, formats, values, formulae, fonts, and column & row widths and heights. I know how to copy & paste everything except the row & column widths & heights. Is there a shortcut way of doing this ? Thanks K Click the grey blank square to the left of column A and just above row 1. Click copy, go to your other sheets and either clcik the gery square or cell A1 and click paste. "KRK" wrote: > hello, > ...

Date format changed
I'm using 2003. The date at the top of the daily calendar only shows 10/23. How can I change it to show the day of week also? The Regionl setting for this computer is good. Thanks. What is the format of the long date setting in Control panel, Regional settings? That is what controls it. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Outlook Tips by email: dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: EMO-NEWSLETTER-SUBSCRIBE...