Excel: calculate dates difference

%3CLINGO-SUB%20id%3D%22lingo-sub-2255990%22%20slang%3D%22en-US%22%3EExcel%3A%20calculate%20dates%20difference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2255990%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20All%2C%3C%2FP%3E%3CP%3EI%20have%20this%20problem%20with%20my%20Excel%20being%20part%20of%20Office%202019%20Professional%20Plus.%20When%20using%20the%20formula%20for%20calculating%20difference%20in%20days%20between%20dates%20(in%20the%20Italian%20version%20the%20formula%20is%20%3DGIORNI%2C%20in%20the%20English%20one%20it%20seems%20to%20be%20%3DDATE.DIF)%2C%20the%20formula%20works%20with%20years%20%3CSTRONG%3Eafter%3C%2FSTRONG%3E%201900%2C%20but%20does%20not%20work%20with%20years%20%3CSTRONG%3Ebefore%3C%2FSTRONG%3E%201900.%20Just%20have%20a%20look%20at%20this%3A%3C%2FP%3E%3CTABLE%20width%3D%22351%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22117%22%3E13%2F10%2F1990%3C%2FTD%3E%3CTD%20width%3D%22117%22%3E25%2F12%2F1991%3C%2FTD%3E%3CTD%20width%3D%22117%22%3E-438%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E13%2F10%2F1790%3C%2FTD%3E%3CTD%3E25%2F12%2F1791%3C%2FTD%3E%3CTD%3E%23VALORE!%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3EHere%20the%20formula%20used%20in%20the%20third%20column%20is%26nbsp%3B%3DGIORNI(A1%3BB1).%20Other%20formulas%20(DATA.DIF%20or%20DATADIF%20or%20DATE.DIF%20or%20DATEDIF)%20are%20not%20accepted.%3C%2FP%3E%3CP%3EI%20have%20already%20tried%20with%20the%20Support%20chat%20to%20verify%20the%20account%20and%20to%20repair%20my%20Office%202019%20Professional%20Plus%3B%20and%20I%20tried%20to%20digitize%20the%20data%20and%20formula%20in%20an%20entirely%20new%20worksheet.%3C%2FP%3E%3CP%3EThanks%20for%20your%20help%2C%3C%2FP%3E%3CP%3EGuido%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2255990%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

Dear All,

I have this problem with my Excel being part of Office 2019 Professional Plus. When using the formula for calculating difference in days between dates (in the Italian version the formula is =GIORNI, in the English one it seems to be =DATE.DIF), the formula works with years after 1900, but does not work with years before 1900. Just have a look at this:

13/10/199025/12/1991-438
13/10/179025/12/1791#VALORE!

Here the formula used in the third column is =GIORNI(A1;B1). Other formulas (DATA.DIF or DATADIF or DATE.DIF or DATEDIF) are not accepted.

I have already tried with the Support chat to verify the account and to repair my Office 2019 Professional Plus; and I tried to digitize the data and formula in an entirely new worksheet.

Thanks for your help,

Guido

1 Reply

@Guido_Abbat1505 Excel dates are held as integers with 1 being 01/01/1900 using dates before this in excel is tricky and not native to excel.  Could you offset all your date with the same value to bring them after 01/01/1900, i.e add 2000 years to each date, this would then allow you to use the datedif function.