Date problems

**JackHammer** · 01-21-2012, 08:59 AM

Having a bit of a problem with Excel, I know the dates only work down to 1900, but is there any easy way around it or an add-on to help?

Using this formula

=DATEDIF(Date1,Date2,"y") & "y " & DATEDIF(Date1,Date2,"yd") & "d"

Most of the dates are in the 1900s, but a few are in the 1890s

**MarvinP** · 01-21-2012, 09:04 AM

Hi JackHamer and welcome to the forum,

See if this helps...
http://www.exceluser.com/explore/earlydates.htm
Or some add-in formulas at
http://j-walk.com/ss/excel/files/xdate.htm

**JackHammer** · 01-21-2012, 10:16 AM

Thanks, couldn't quite understand how to make the first one work, getting quite close with Xdate

Using this formula now, slightly off, because of the leap years

=XDATEYEARDIF(A1,B1) & "y " & ((XDATEDIF(B1,A1)-365*XDATEYEARDIF(A1,B1))-XDATEYEARDIF(A1,B1)/4) & "d"

Probably as close as I'll get, is there any way to round the last part up, or clean it up so it's more exact, as I'm getting half a day?

Tweaked it again

=XDATEYEARDIF(A1,B1) & "y " & ROUND(((XDATEDIF(B1,A1)-365*XDATEYEARDIF(A1,B1))-XDATEYEARDIF(A1,B1)/4), 0) & "d"

Accurate to within a day, will do for now

**MarvinP** · 01-21-2012, 10:32 AM

Hi Jack,

I don't understand that "half day" problem. I do know that the first leap was legislated - see http://answers.yahoo.com/question/in...6163524AAz0pdX
Are you running into this problem of 11 missing days?

**daddylonglegs** · 01-21-2012, 10:56 AM

Try this for more accuracy

=XDATEYEARDIF(A1,B1) & "y " & XDATEDIF(B1,xdate(xdateyear(A1)+XDATEYEARDIF(A1,B1),xdatemonth(A1),xdateday(A1))) & "d"

....if you want you can probably get the result without using any add-ins, as long as your pre-1900 dates are in a consistent format, what do they look like?

**JackHammer** · 01-21-2012, 11:04 AM

Had half a day because I was dividing the years (50 in this case) by 4 to remove the extra days, now I'm rounding it it's fine, as close to perfect as I'll probably ever get

**JackHammer** · 01-21-2012, 11:08 AM

Originally Posted by daddylonglegs

Try this for more accuracy

=XDATEYEARDIF(A1,B1) & "y " & XDATEDIF(B1,xdate(xdateyear(A1)+XDATEYEARDIF(A1,B1),xdatemonth(A1),xdateday(A1))) & "d"

....if you want you can probably get the result without using any add-ins, as long as your pre-1900 dates are in a consistent format, what do they look like?

That's even better, cheers