How do I get Excel to recognise dates prior to 1/1/1900 ?

As title asks. Family Tree information finds some earlier dates fail to be 
recognised and formatted as such.
0
Gazz (4)
11/16/2005 10:15:44 PM
excel.misc 78881 articles. 5 followers. Follow

8 Replies
383 Views

Similar Articles

[PageSpeed] 33

Excel does not recognize pre-1900 dates as other than text.  You might check 
out this add-in from John Walkenbach which will help:

http://www.j-walk.com/ss/excel/files/xdate.htm

-- 
Jim
"Gazz" <Gazz@discussions.microsoft.com> wrote in message 
news:B49C14D4-55CE-4972-8E8D-87420F81C14D@microsoft.com...
| As title asks. Family Tree information finds some earlier dates fail to be
| recognised and formatted as such. 


0
jrrech (1932)
11/16/2005 10:28:32 PM
See http://j-walk.com/ss/excel/usertips/tip028.htm

Always type full question in the message space not the subject line

-- 
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Gazz" <Gazz@discussions.microsoft.com> wrote in message 
news:B49C14D4-55CE-4972-8E8D-87420F81C14D@microsoft.com...
> As title asks. Family Tree information finds some earlier dates fail to be
> recognised and formatted as such. 


0
bliengme5824 (3040)
11/16/2005 10:31:23 PM
I would recommend that you use text so that they will not change; however,
if you want to risk using a macro John Walkenbach  wrote
Extended  Dates    routines

Excel does not recognize dates before  Jan 1, 1900 and mishandles leap year in 1900
so for simply calculating date differences (age) you might use  John Walkenbach's
XDATEDIF Extended Date Functions Add-In,  instead of   DATEDIF, eliminating problems
with negative dates involving subtraction in MS date system and incorrect leap years in
older MS 1900 date system.
  http://www.j-walk.com/ss/excel/files/xdate.htm

You calculations can be further complicated by when a country switched calendars.

HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

"Gazz" <Gazz@discussions.microsoft.com> wrote in message news:B49C14D4-55CE-4972-8E8D-87420F81C14D@microsoft.com...
> As title asks. Family Tree information finds some earlier dates fail to be
> recognised and formatted as such.


0
11/16/2005 10:37:46 PM
Gazz

See John Walkenbach's site for working with dates prior to 1900.

Download his Extended Date add-in.

http://search.atomz.com/search/?sp-q=dates+prior+to+1900&sp-a=000336ae-sp00000000


Gord Dibben Excel MVP

On Wed, 16 Nov 2005 14:15:44 -0800, "Gazz" <Gazz@discussions.microsoft.com>
wrote:

>As title asks. Family Tree information finds some earlier dates fail to be 
>recognised and formatted as such.

0
Gord
11/16/2005 10:54:09 PM
Excel's normally formatted dates only go back that far.  You can use custom 
add-ins or just use Julian dates ( without fancy formatting and support 
functions.
-- 
Gary's Student


"Gazz" wrote:

> As title asks. Family Tree information finds some earlier dates fail to be 
> recognised and formatted as such.
0
GarysStudent (1572)
11/16/2005 11:44:03 PM
Thanks all.  Lots to go on there.

Not expecting dates to change as they're not used in calculations.
That column just indicates briths, marriages, deaths, etc..

Might be better in a database really, but I'm more used to Excel.

Will look up the links etc. this evening.

Cheers
0
Gazz (4)
11/17/2005 7:51:03 AM
Ah ha ! Took a while to work though the "Insert / Function / Date&Time" 
stuff, think the diagram is for an earlier version of Excel.

Doesn't work automatically I see, has to be entered 
=XDATE(year,month,date,"format"), but hey ! It works :-)

A quick Global Replace should sort things out : sorted, thanks.
0
Gazz (4)
11/17/2005 1:04:26 PM
Ah and I have now discovered how to send a reply to one and not all :-)

Live & learn.
0
Gazz (4)
11/17/2005 5:16:06 PM
Reply:

Similar Artilces:

Are there any household budget templates available in Excel?
Are there any household budget templates available in Excel? http://office.microsoft.com/en-us/templates/default.aspx Do a search near the top for "Budget," and you should find a few of them. Looks like "Personal budget" is the most popular, followed by "Family monthly budget." (based on votes) HTH Dana DeLouis "nevinnh" <nevinnh@discussions.microsoft.com> wrote in message news:2C0B084E-954F-4CAF-8ACB-AF33C6F3F097@microsoft.com... > Are there any household budget templates available in Excel? Go to microsoft template site, on the sear...

Symbols in Excel 2007 Chart
Dear Group, Hello. I would like to use the greek mu symbol in an Excel 2007 histogram. I am able to convert the "m" into the proper "mu" symbol in the spreadsheet itself but this is not reflected in the resulting graphic. I need for my X axis label to be represented by the proper greek letter, not m. Does anyone know how to fix this issue? Thank you in advance. John McLaughlin In many fonts, holding Alt while typing 0181 on the numeric keypad produces �. This means you don't have to change the font for some of the characters in a text element (and many tex...

Getting a subform control to requery
Main Form : F_BU Subform1: F_BU_Cat2 SubForm2: F_BU_Cat3 When I click on one of the records in subform1 (field BillCat) the OnClick event uses the value in the field as a criteria in a query that is used in the combox (cbxCat3) in Subform 2. Here is the code I was trying to use: Me.Parent!F_BU_Cat3.Form!cbxCat3.Requery It was working for a while but now not when I click on the "BillCat" field in Subform 1 I get: Method: Form" of Object ' _Subform' Failed. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-form...

=?iso-8859-1?Q?=22microsoft_office_document_imaging=22_documents=3F?= #2
Is there a program available for Mac that can read "microsoft office document imaging" documents? I have a number of email attachements sent to me with "filename.mdi" that I need to open Can they be opened on a Mac running OS X? ...

Excel 2000 tries to open unrecognizable file on startup
Hi, For the last couple of weeks, when I first open Excel I get a Microsoft Excel Error pop-up window that says "This file is not in a recognizable format....". I am uncertain what file it is trying to open, but when I click "cancel" it opens books1.xls. Which I usually ignore and go into "open file" and pick my excel file. Any idea what is going on? Win98 Excel 2000 -- Thanks - Jeff Jeff I suspect one of two things 1) Excel opens all files in it xlStart folder. Search for this and remove anything without an .xls, xlt extension 2) If you go to Tools&g...

We are getting stat=Deferred: Connection reset errors .....
Does anyone know how to fix this issue? I am getting a lot of emails BUT an Exchange admin from another company that sends SMTP email to is saying that he sees a lot of stat=Deferred: Connection reset error messages in his log file. very very weird. Has anyone seen this issue? He can telnet to the server fine etc... plus I am getting email from his company but it is deferring aa ton though. Thanks What version of Exchange and SP level Oliver Exchange 5.5 SP 4 is our IMS. "Oliver Moazzezi" wrote: > What version of Exchange and SP level > > Oliver > >...

.pst file is maxed out - HELP
Apparently I have reached the maximum size of my outlook.pst file (see attached error message). I have tried deleting items but nothing I do seems to have any effect and I continue to get the attached error message. Therefore the eMail part of Outlook is disabled. Anyone got any pointers? XP Pro SP-2/Outlook 2003. Already using .pst format for database. Thanks, No image attached. Post a link, rather than an attachment. See http://www.slipstick.com/problems/repair2gbpst.htm=20 --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/o...

combine multiple excel file in to one excel file and multiple worksheet
I am wanting to use the following code to combine worksheets from multiple files. However I would like to be able to select folder which contains files in a more automated way that having to change the code every time, and also copy all worksheets with links and formulas removed. Any help on this is greatly appreciated as I have limited code knowledge. Sub Copy_them() > Dim TargetWkbk As Workbook > Dim mrgWkbk As Workbook > Dim i As Long > Dim Wks As Worksheet > Dim fName As String > Application.ScreenUpdating = False > Set TargetWkbk ...

"10th" on Excel
For the past few weeks, any time I enter the number "10", the Excel program automatically adds "th" making it "10th". This is also occuring on the Word program. I don't remember making any changes to either toolbar- HELP!!! I can think of one possibility, (and don't ask me how it may hav happened), but look in Tools - Autocorrect and see if you can find "10 shown as being replaced by "10th". My program once started replacin all lower case "p"s with upper case and I know it was nothing I did. If that isn't the problem, I ca...

Automatic backup in excel with copy to hard drive and to cd-rw
How do I get Excel to either automatically backup to hard drive and cd-rw, or even when I hit save icon, go ahead and save to hard drive and cd-rw. I already have auto save setup but backup file goes to same hard drive folder as origianl copy. Kind of defeats the purpose of a system crash and wanting a copy on removable media. Thanks for your help! This Word macro auto-saves a copy to a floppy drive. You should be able to edit it to save to the hard drive and CDRW, too. You'll also change some stuff to make it an Excel macro: http://www.vbaexpress.com/kb/getarticle.php?kb_id=203 *...

can I get "step by step" on color seperating for commercial printi
This is kind of an extension on my previous message but I feel it would help a lot more. Can someone give me a quick step by step on making a document with text and photo into a press quality color seperated piece using PMS colors and process black? Or maybe point me to a site that can give step by step on that stuff? ...

Where can we get xml scheme for tuning setting of WM2003/WM5.0 PDA
Hi all, We are working upon setting up a lot of WM2003/WM5.0 empowered mobile devices according with company standards. As far as I know these settings are tuned through xml file. Then this xml file has to be deployed deployed/executed onto mobile device. My question is where xml scheme for tuning such setting is published so we may assign necessary properties referencing it? Thank you in advance. ...

Why does Excel ask to save when no changes were made?
This does not pertain to a new document. I open an existing document just to look at it, and when I go to close it, Excel asks if I want to save the document. No changes were made, so it shouldn't have to be saved. It's annoying. Mike One explanation could be that there is a time formula in a cell that has updated on openeing so the worksheet will have changed. -- Mike When competing hypotheses are equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. Occam''''s razor (Abbrev) &...

My excel file is too large (5 mb) when it shouldn't be, why?
I have an excel file has ballooned in size for some reason. I have tried to copy all the cells and then used "paste special" to paste just the values and the file size doesn't reduce. I am copying numbers from an investment website into my spreadsheet and there don't appear to be any issues with the pasted cells but I suspect it is causing the file size expansion and very slow performance. I then deleted all the cells around by spreadsheet and it didn't help at all. All suggestions as to how I can fix this problem appreciated. Kenview If you do a Ctl-End, does...

Excel & Word auto selection from menus
Ok, I'm a keyboarder, not a mouser. I use quick commands like the letter code or arrows on menus to speed along without the grab, aquire, click business. Excel & Word seem to be periodically (not always) automatically selecting menu items based on prior selections if I use arrow selectors. How do I turn this off? I get no help from "Help". Please reply to my e-mail address: legreenwood@hotmail.com. Thanks! Would love to get rid of this nuisance & keep speeding along. ...

How to get Global Notification of browser request...
Hye, I am trying to get notification of any browser request... (not only that of the local server.. so filters will not be the right way to solve problem) So, whenever any request like (http://www.yahoo.com/) is made in IE, I want my DLL routine or my EXE should be called.. So, how to do that, any suggestion will be highly appreciated... -- Jigar Mehta jbmehtain@yahoo.co.in the program you need is called Browser Helper Objects http://msdn.microsoft.com/library/en-us/dnwebgen/html/bho.asp -- Command what is yours Conquer what is not Microsoft Most Valuable Professional [VC++] h...

Creating Excel Invoice Template
I have been trying to customize a downloaded template but was frustrated so trying to create my own invoice. My question is once I input the formulas how do I keep/save them ???? when I enter the first calcualtions all is well, but if I go back and edit in the cell where the formula resides I lose the formula ??? I am a relative newbie to this so any help will be greatly appreciated. I have searched this site and cannot find a link to creating an invoice worksheet. Sasha ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions wit...

[ANN] New installer for 12.0.1
MacBu released a new installer for the Office 2008 Update 12.0.1 today. This is not new Office bits to be installed, but the installer itself that was changed. This change was a result of problems experienced by some Office 2008 users having a failed update to 12.0.1. If you are one of the unfortunate few that had problems, this patch is for you. If you were able to successfully update to 12.0.1 then you do not need this patch. Continue reading for steps to properly update Office 2008 to version 12.0.1. -- Diane, Microsoft Mac MVP (MVPs are not Microsoft Employees) Entourage Help Page <...

Sending Email in Excel
1.) I have a formula in a cell that when clicked, sends an email to recipients in the formula. I have pasted the formula below. What I want to do is put the date after the "End of Day: " part. However, when you click on the link, it gives the serial representation of the date if you use today(). Is there anyway to format so that is shows the date formatted correctly? In cell A1: =HYPERLINK(CONCATENATE("mailto:Person1@abc.com; Person2@abc.com?Subject=End of Day - ",A34,": Acc: ",AB1," for ","$",AA1," | Feats: ",'Enh Feats'...

Too Few Parameters, 1 expected
I'm getting the error too few paremeters, 1 expected with the code below. I'm getting the error at " Set rst3 = dbs.OpenRecordset(strSQL3)". Any help would be much appreciated On Error GoTo Err_cmdCreateRecords_Click Dim intResp As Integer Dim intCount As Integer If ReportDate = "" Or IsNull(ReportDate) Then MsgBox "Please enter a valid report date." ReportDate.SetFocus GoTo Exit_cmdCreateRecords_Click End If intResp = MsgBox("This function will add records for the reporting date specified. Do you wish to continue?", vbO...

Emails getting stuck in Outbox
Hi I am sending a number of emails (around 600) using the vb.net code below. The problem is that the emails end up in the OL2007 Outbox with a clock icon and do not go out. If I open one of the email items and click Send then that item goes out but this technique is too cumbersome for 600 or so emails. What is the problem and how can I fix it? Many Thanks Regards Dim objOutlook As Object = CreateObject("Outlook.Application") Dim NS As Object = objOutlook.GetNamespace("MAPI") Dim objOutlookMsg As Object Dim BodyText As String ...

Excel 2000: Regional date problem
We'e just upgraded from Excel 97 to 2000 and are having problems because dates in spreadsheets are showing in US (mm/dd/yy) format rather than in European (dd/mm/yy). This si true whether I use a standard date format or make a custom format. I've checked the regional settings in Windows 2000, and they are correct. I thought Excel picked up the default from here. Any other ideas where I might look? Thanks in advance Alan I wasn't able to duplicate your problem, Alan. I switched to UK settings through Regional Settings. Then I opened Excel 97 and entered =NOW() in two ce...

Date format problem #2
Dear All, Please suggest me the solution of my problem, as i m handling data for 60,000 in no.Some body in data entry has enter the date in a cell in gernal format e.g 20.03.2003 tht is 20th march 2003, i wnt to convert in the date fomat,like 3/20/03.. i tried ..but i failed to tht please suggest some alternative solution for tht..becoz it is not possible to do it mannually waiting for the response Thanks NISHANT Nishant Try using Data/Text to Columns. and assign the information as a date. Andy. "Nishant" <nishant.khare@wipro.com> wrote in message news:090201c37ce0$1b...

Excel countif and
is there a "and" condition if you use countif or sumif? example; 1 c 1 1 c 2 2 < countif(b1:b4,"=c")and(a1:a4,"=1") Hi! Try this: =SUMPRODUCT(--(A1:A4=1),--(B1:B4="C")) Biff "KEN" <KEN@discussions.microsoft.com> wrote in message news:02a801c54ae1$3371a4f0$a401280a@phx.gbl... > is there a "and" condition if you use countif or sumif? > example; > > > 1 c > 1 > 1 c > 2 > 2 < countif(b1:b4,"=c")and(a1:a4,"=1") > ...

Percentage formula in Excel spreadsheet
Hi, In my spreadsheet cell E14 adds up E2 - E14 and cell C14 adds up C2 - C14. In G14 I have put a formula to show the difference between E14 and C14 as a Percentage ( formula for G14 is =SUM(E14-C14)/ABS(E14) ). When the Formula for G14 looks at E14 and C14 all it sees is a SUM formula to add up the column. Does anyone know how to solve this problem. Many thanks for looking. Rick Not sure I understand the problem; tell us what answer or error you ar getting. Also, don't you mean you are summing C2:C*13* in C14 and E2:E*13* i E14? Oh, and you can get rid of the Sum - this ...