problems with imported dates

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Power PC

I am importing data with dates that range from 1887 to 1950s.  Excel keeps the 1880s dates, but either subtracts or adds 4 years and one day to all of the 1900s dates.  I have tried reformatting the dates in the source information, reformatting in excel and copying to another spreadsheet, using different date formats to move the data, etc.  nothing seems to work. <br><br>If there is anything this program should do, its process dates correctly.  This is a major issue when accuracy is involved and is worse than manually adding all the information!
0
svkr
5/6/2010 5:02:14 PM
mac.office.excel 1146 articles. 0 followers. Follow

3 Replies
919 Views

Similar Articles

[PageSpeed] 36

Check to see what date system you have set. On Apple its 1904, on PC 1900.  If your switch from PC to apple you have  use the same system from the orignating computer.
0
pjonesCET
5/6/2010 6:22:36 PM
Thanks, that sounds close, where do I find those settings? <br>
I am only importing from my own filemaker database, which is data I entered, and It appears correctly on the first import to excel (new worksheet), the final destination workbook must have originated on a PC.  Can I change the date system on one workbook only?
0
svkr
5/6/2010 6:33:35 PM
Are you actually *importing* or are you using either copy/paste or linking? 
If either of the latter, that's what's causing your problem. If you export 
from FMP as an Excel file or as a Text file you can *open* the file in any 
version of Excel & the dates will be correct.

Dates are handled by Excel as serial number values beginning with either Jan 
1, 1900 or Jan 1, 1904 depending on the Date System used in the workbook 
file. Windows Excel uses the 1900 Date System as the default, Mac Excel uses 
the 1904 Date System. HOWEVER, that should make no difference since the 
setting is workbook-specific & both Date Systems are supported in both the 
Windows & Mac versions of Excel. IOW, if you *open* the file in any version 
of Excel the existing dates & any that you enter will be correct. If you 
*copy* [or Link to] them, however, it's the serial value that gets read & 
entered, so if the target workbook is set to the other Date System the 
displayed dates will be off by 4 years & 1 day no matter what 'formatting' 
is used.

Changing the date system will not resolve the problem if there are already 
dates in the file - the pasted ones may be correct but the pre-existing ones 
will then be wrong. It isn't difficult to deal with if you have a look at 
the material in this KB article: http://support.microsoft.com/kb/180162

-- 
HTH |:>)
Bob Jones
[MVP] Office:Mac


<svkr@officeformac.com> wrote in message 
news:59bb8129.1@webcrossing.JaKIaxP2ac0...
> Thanks, that sounds close, where do I find those settings?
> I am only importing from my own filemaker database, which is data I 
> entered, and It appears correctly on the first import to excel (new 
> worksheet), the final destination workbook must have originated on a PC. 
> Can I change the date system on one workbook only? 

0
CyberTaz
5/6/2010 9:15:23 PM
Reply:

Similar Artilces:

Problem with post callout
Hi. I'm able to create files on the server as response to accounts being created in CRM. However, I'm not able to load other .NET assemblies or applications from the same component. Any idea why? Are those other assemblies strong named? If not, you either need to make them strong named or create a wrapper. I know you can do this with a utility, but don't have the steps handy. Matt Parks MVP - Microsoft CRM ---------------------------------------- ---------------------------------------- On Fri, 8 Oct 2004 07:21:02 -0700, "Paul" <Paul@discussions.microsoft.com>...

Outlook problems after WinXP updates
I installed critical updates for Windows XP on my laptop Wednesday. Nowmy Outlook hangs and I am unable to send new messages or reply to messages while using VPN. Any ideas? Suggestions? ...

I am having problems with "Calendar"
Hi all I'm trying to make a calendar that cycles (January February March etc.) starting from 2010 to when ever in MS Publisher. I have Calendar Creator by Broudbund that does this but whenever I converted to a PDF file, the file for each month is a little under 5 MB (I think because it is created as a JPEG file) which is not good for my application. Whenever I convert the MS Publisher "Calendar" to a PDF file it runs about 72 kB. But I haven't figured out how to sequence the program where it will automatically make a calendar page for each month while carrying o...

OWA display problem
Our Outlook is performing normally when accessed internally. When accessing by way of OWA, however, we can see the headers, but cannot open the message. We can compose new messages and send them. We cannot see the message body in a preview pane. Any ideas? Steve, It sounds like you might have URLScan installed on the OWA server. Please have a look at the following article to see if it is relevant. 320089 XCCC: The URLScan Utility Does Not Allow You to Open Messages in OWA http://support.microsoft.com/?id=320089 Hope this helps. -- Greg Mansius [MSFT] This posting is provided &...

Need help converting date time to date only
I have a column in my spreadsheet with date and time, you can see in m attachment. I want to convert this column in to date only. Pleas help. Thanks Geng Attachment filename: sales - open orders test.xls Download attachment: http://www.excelforum.com/attachment.php?postid=57210 -- Message posted from http://www.ExcelForum.com Hi without looking at your file you could use the following formula in a helper column =INT(A1) and format this cell as 'date' -- Regards Frank Kabel Frankfurt, Germany > I have a column in my spreadsheet with date and time, you can see in ...

Take a look at important patch from the M$ Corporation
--bgknsnsxrf Content-Type: multipart/related; boundary="eqmmxvfryf"; type="multipart/alternative" --eqmmxvfryf Content-Type: multipart/alternative; boundary="nagrsturcwerjp" --nagrsturcwerjp Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Consumer this is the latest version of security update, the "October 2003, Cumulative Patch" update which eliminates all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express. Install now to maintain the security of your computer. This update...

Problems with the installation
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Had a copy of Mac:Office 12.0.0, but couldn't ever upgrade it successfully. Finally bought another copy (digital download) of 12.1.6 to install - had already moved the other to a 'safe location'. The install completes successfully, however, when attempting to open any of the Office programs I get the following error: "A problem has been detected with the Microsoft Office installation." and "try running the Office Installer again to resolve the problem". I have run the installer four add...

Inserting Rows Problem
Hi there, I have a list of about 2000 rows. However, in between each of thes rows I want to insert a blank row that can be used for people to writ in once the list is printed off. Is there a quick way to do this? Kind regards Andre -- andyp16 ----------------------------------------------------------------------- andyp161's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1165 View this thread: http://www.excelforum.com/showthread.php?threadid=26611 If you insert rows it will affect sorting, etc. and be a real pain. Many posts here want to delete blank rows ...

Importing Data From Access Problem
I'm trying to import some query results from MS Access into Excel using the Import External Data function. Some of the queries are coming across OK, others are only bringing the column headers across and some are bringing partial data. Does anyone have any ideas why this may be happening? Thanks Helena ...

custom header date formats
How can I set the date format on a custom header? I know I can use &[date] to insert the date...the problem is I want the date to display in a specific format (ex: MMDDYY or MMDDYYYY) Thanks! Ron --- Message posted from http://www.ExcelForum.com/ Use something like: & TEXT(Today(),"d-mmm-yy") HTH Otto "Ron777 >" <<Ron777.102ysm@excelforum-nospam.com> wrote in message news:Ron777.102ysm@excelforum-nospam.com... > How can I set the date format on a custom header? I know I can use > &[date] to insert the date...the problem is I want the...

Problem consuming a webservice
Hello all. I have a problem I cannot figure out. I have an ASp.NET we app that receives names of bible books. I get an "*Illegal character in path*" error. My codes look like this: net.webservicex.www.BibleWebservice ws = ne net.webservicex.www.BibleWebservice(); dsBible.ReadXml(ws.GetBookTitles()); <----------this is where th error is DataGrid1.DataSource = dsBible; DataGrid1.DataBind(); Now, if I save the contents into a xml text file, my datagrid render the data fine. My suspicion is the webmethod is sending me escap sequences (CR, LF, etc..) and if its saves onto a text f...

Namespace problem
I've the following code, taken from http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexxml/html/xml02172003.asp to select a node from RSS file. However, I'm always getting a null returning for the SelectSingleNode(), why? if(feed.DocumentElement.LocalName.Equals("RDF") && feed.DocumentElement.NamespaceURI.Equals("http://www.w3.org/1999/02/22-rdf-s yntax-ns#")) { NamespaceUri = "http://purl.org/rss/1.0/"; } else if(feed.DocumentElement.LocalName.Equals("rss")) { NamespaceUri = feed.DocumentElement.NamespaceURI; } ...

displaying dates in UK format
I didn't write the original database and my knowledge of database design is not good enough to duplicate the work already done. With a variety of computers running Windows98 and/or XP, using Access2003, I cannot get dates to display on my forms in UK format (i.e. dd/mm/yyyy or even d/m/yy). The computers in use all have the short date format set to dd/mm/yyyy in regional settings. I have read through http://allenbrowne.com/ser-36.html and tried the formatting suggested there and it didn't work for me. I added it to "Lost focus" and "OnExit" and ...

Integration manager VBScript email problem
I am doing something wrong with my Document Error script and I can't pinpoint the problem. This script should send an email when a document error is encountered with a link to the log file (I would really rather send the file as an attachemnt, but I was never able to get it to work so this is the second best option). Everything with this script works until I add the file link in the objMessage.HTMLBody. It will send the message, however, the message just says "False". Can someone tell me what I'm doing wrong? Thank you! 'Set path to the log files Set pFSO = ...

Public Folder replication problems Exchange 2003
We have two Exchange 2003 sites between which we replicate some public folders. This was working fine until recently, when one folder stopped replicating in one direction - i.e. postings made to the replica appear in the original folder, but not vice-versa. At the same time we started receiving numerous 1110 Events: Error 0x4ed occurred while writing per-user information for a.user@company.co.uk on database "First Storage Group\Public Folder Store (SERVER)". The event is repeated multiple times - I assume once for each user, though I haven't actually checked. If neces...

Active sync problem in wince5.0 when de-activate "Enable KITL" in release mode
Hi, My Usb fucntion driver with active sync support in wince 5.0 is working fine with de-activate "Enable KITL" support in debug mode. By de-activate "Enable KITL" in RELEASE mode, active sync is not working. Any idea ? Regards - D. Does it work if you Enable KITL in a release build? Do you have ActiveSync included in your release build? What are you using as your default USBF client? Check your COM ports, the COMx x number may have changed. -- Bruce Eitman (eMVP) Senior Engineer Bruce.Eitman AT Eurotech DOT com My BLOG http://geekswithblogs.net/brucee...

problems with right click in Office
When I click and/or right click on a Word or Excel file, it takes a loooong time to open. Once you open excel sheet and use "OPEN", it will quickly open. Help please -- neally When you click on a document Windows has to translate the file extension into the application. This might be a windows problem or a problem with how Excel was installed. Let's assume it's not and try looking at the article I found online. It's generally better to work with what you've got then start from scratch.. http://www.smartcomputing.com/Editorial/article.asp?article=articles/2...

Formula Problem
Hi I'm struggling with a formula in an excel sheet I'm working on. I recently created a time sheet for the people in my work. One person however get public holidays a different method from everyone else - she only gets half a working day. My initial formula that works fine is =IF(OR(B12="A/L",B12="S/L",B12="P/H"),7/24,IF(B12="FLEXI",0,C12-B12- D12)) My ammended ones is =IF(OR(B12="A/L",B12="S/L"),7/24,IF(B12="P/H",3.5/24),IF(B12="FLEXI", 0,C12-B12-D12)) However it doesn't want to w...

Customer Membership Expiration Date
I have a customer who sells annual memberships to museum patrons, they get a discount in the gift shop. She would like to set the expiration date on the membership at the time she sets up the customer in the database. Does anyone have any ideas how to accomplish this? Add-Ons available? Thanks for your time and knowledge. You can use one of the customer custom date fields, set it equal to the expiry date. Then customize the HTML status bar to indicate whether the date in that field is less than today. Easy as pie, although if you have every tried making a pie it is actually very h...

Filtering problem
Hi everybody, it`s my first posting on this forum. My colleague made a document in Excel 2000 and when I tried to open i it was unpossible to open the filterin option /it was visiable, bu clicking on the option was without any results/. The same thing was o the other computer, but on her`s everything was working perfectly well What do you think about it ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ~~Now Available: Financial Statements.xls, a step by step guide to creati...

?problems printing from certain browsers
On my laptop with Vista Home Premium I had not used any physical printers or their software, but did use a few print-to-pdf programs for printing to files. Recently I installed (a)software so it could access two (identical)Epson printers I have always used with my PC. All of the printing goes well as long as I print from a Windows Explorer file with Notebook-type apps, and probably Irfanview as well. But when I am online with IE7 or Firefox and click to print something from a webpage nothing works, not even my print-to-pdf anymore -- instead I get an error saying the browser...

Importing data from Maximizer
Hi: What experience has anyone had with importing Maximizer data into MSCRM using either the Data Migration tool, Scribe, or a combination of both? Scribe don't handle this seamlessly, you apparently need to export to a .csv file first. Is this still quicker using this process with Scribe, than with the DMF? Many Thanks Cathy Allington Hi, We are also looking at migrating Maximizer into CRM. If you would not mind, could you please let me know if you find anything more on this. I would truly appreciate it. Shauna skoppang@shaw.ca "Cathy Allington" wrote: > Hi: > ...

Import List of Email Addresses from Access
Is there a way to populate a distribution list in Outlook from a table field in Access? Thanks in advance, Claudette ...

SnapShot Date?
RMS v1.3.0203 This is for HQ looking at 114 active SO stores. The report is generated in HQ> Rpts> Items> SnapShot Store Price List. What is it that defines the snapshot date? I always looked at it from the inv side post physicals, request item count and reconciliation - but now I am looking at store level pricing (they set their own). I can see the snapshot date is different not only on different stores, but also on different line items from the stores. So far, my best guess has been that the snapshot date is based on a date the store has set or changed their pricing (like p...

IF formula problem
I need to add categories to a downloaded bank statement. I would use something like Pascal's CASE formula: CASE(lookup value)= a: do x b: do y c: do z etc... Excel doesn't have such formula. Therefore, I use the following nested IF formula: =IF(ISERROR(SEARCH('Netbank Cat names'!$A$2,B73)),IF(ISERROR(SEARCH('Netbank Cat names'!$A$3,B73)),IF(ISERROR(SEARCH('Netbank Cat names'!$A$4,B73)),IF(ISERROR(SEARCH('Netbank Cat names'!$A$5,B73)),IF(ISERROR(SEARCH('Netbank Cat names'!$A$6,B73)),IF(ISERROR(SEARCH('Netbank Cat names'!$A$7,B73)),"&...