DATEDIF and count of days earlier than 1900

@ > Home > Contents > DATEDIF and count of days earlier than 1900 Excel 97+

Task:

Get DATEDIF or the count of days between any date from 1/1/0 and 12/31/7999. Type the date ['][M]M/[D]D/[Y][Y][Y]Y as string or date. The [] parts are optional. May 4th of the year 4 must be typed as '5/4/4; otherwise Excel would convert it to 5/4/2004. 5/4/1872 will be automatically entered as string.

Solution:

B1: =IF(ISNUMERIC(A1),A1+730485,
--(LEFT(A1,SEARCH("/",MID(A1,4,9))+3)&MID(A1,SEARCH("/",MID(A1,4,9))+4,9)+2000))

Copy to B2 and use

=B2-B1-(B1<614627)*(B2>614626)*10 for the count of days or
=DATEDIF(B2,B1,...), see also here

Format B:B als M/D/ - to prevent from misinterpretation!

Remarks:

http://www.excelformeln.de/formeln.html?welcher=1 (in German) is a reference even for years A.D.; http://en.wikipedia.org/wiki/Gregorian_calendar or this show further details.

The 10 days' correction at beginning of the Gregorian calendar are calculated for the count as -(B1<614627)*(B2>614626)*10 (only for countries following Gregor XIII immediately like Italy. Other countries need a different term here). However, they should not be considered within DATEDIF!