## [Solved] Replacing DATEDIF in an Excel equation?

### [Solved] Replacing DATEDIF in an Excel equation?

I'm trying to open an Excel document with the following formula:

=DATEDIF(A4,NOW(),"D") (actual example from row 4)

The formula calculates the age of a row's entry in days by substracting the date in the first cell (A4) from the current date.

In OpenOffice the formula opens as a =#NAME!(A4;NOW();"D") error because OpenOffice doesn't support DATEDIF.

Is there a way to rewrite this equation somehow to make it both Excel and OpenOffice compatible? Thank you for the help in advance
Last edited by mrdelurk on Fri Aug 15, 2008 3:02 am, edited 1 time in total.
OOo 2.3.X on Ms Windows XP + OSX in the future
mrdelurk

Posts: 2
Joined: Thu Aug 14, 2008 10:13 pm

### Re: Replacing DATEDIF in an Excel equation?

=NOW()-A4 returns the difference between now and a time in A4 since all spreadsheets calculate times as numbers in unit "days".
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice

Villeroy
Volunteer

Posts: 29727
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: Replacing DATEDIF in an Excel equation?

mrdelurk wrote:=DATEDIF(A4,NOW(),"D") (actual example from row 4)
The formula calculates the age of a row's entry in days by substracting the date in the first cell (A4) from the current date.

I searched Excel help and function reference, and MS knowledgebase. Can't find any reference to the DATEDIF() function. Is it a user defined function? (The reason I miss it may be that the searches are filtered according to my language settings.)

However, time and date values are stored internally in spreadsheets as "days passed since day zero" (sometimes referred to as the "epoch"), so as you suggest, a simple subtraction should do the trick. That will work in Excel too. In any case you may need to round it.

There are also a couple of functions in Calc:
DAYS() does that subtraction I mentioned. DAYS360() returns a similar result, but based on an imagined "360 day year". It uses a "skew", and may be used to smooth out calculations of monthly interests.

Note that if the time of day is not important, you should probably use TODAY() instead of NOW().
Apache OO 3.4.1/4.1.2/4.1.3, on Ms Windows 7/10 and Mac OS-X 10.8.5 and 10.11.
LibreOffice 4.3.3.2 on Mepis Antix MX 14

keme
Volunteer

Posts: 3406
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

### Re: Replacing DATEDIF in an Excel equation?

Microsoft does not document this function for a reason I don't know. It seems to be somehow inofficial. It is documented as Excel compatible function for the Gnumeric spreadsheet http://www.gnome.org/projects/gnumeric/ ... EDIF.shtml
There is no reason for DAYS(end;start). DAYS(end;start) is exactly the same as end-start.
DATEDIFF(\$A2;\$B2;"D") <=> INT(\$B2-\$A2) [INT because datediff cuts off fractions of days]
DATEDIFF(\$A2;\$B2;"M") <=> 12*(year(\$B2)-year(\$A2))+month(\$B2)-month(\$A2)-(day(\$B2)<day(\$A2))
DATEDIFF(\$A2;\$B2;"Y") <=> =int(yearfrac(A2;B2))
DATEDIFF(\$A2;\$B2;"Y") <=> =year(\$B2)-year(\$A2)-if(month(\$A2)>month(\$B2);1;if(month(\$A2)=month(\$B2);day(\$A2)>day(\$B2);0))
DATEDIFF(start;end;mode)has a third argument which allows for seveal types of financial years and a value of 1 for exact years. But even the latter returns wrong results in some cases; such as:
A1: 2001-01-01
=YEARFRAC(\$A1;DATE(YEAR(\$A1)+3;12;31);1)

I don't find a quick solution for YM, MD and YD. Too tired.
 Edit: Got it. See my next posting in this thread
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice

Villeroy
Volunteer

Posts: 29727
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: Replacing DATEDIF in an Excel equation?

I replaced the equation with

=TODAY()-A4

and now it works in both programs.
OOo 2.3.X on Ms Windows XP + OSX in the future
mrdelurk

Posts: 2
Joined: Thu Aug 14, 2008 10:13 pm

### Re: [Solved] Replacing DATEDIF in an Excel equation?

I tested the DATEDIF replacements in Gnumeric since it has a documented version of DATEDIF which is announced to be Excel compatible. I compared my functions with all variations of datedif between a fixed start-date compared to 10,000 end-dates increasing by one day and fixed my formulas until there were no more differences. I'll attach my test-sheet here as xls, just in case somebody cares to test with Excel.
My equivalents for all 6 variants of DATEDIF are:
Code: Select all   Expand viewCollapse view
`DDd =DATEDIF(\$A2;\$B2;"d") =INT(\$B2-\$A2)DDm =DATEDIF(\$A2;\$B2;"m") =12*(YEAR(\$B2)-YEAR(\$A2))+MONTH(\$B2)-MONTH(\$A2)-(DAY(\$B2)<DAY(\$A2))DDy =DATEDIF(\$A2;\$B2;"y") =YEAR(\$B2)-YEAR(\$A2)-IF(MONTH(\$A2)>MONTH(\$B2);1;IF(MONTH(\$A2)=MONTH(\$B2);DAY(\$A2)>DAY(\$B2);0))`

Variants DDym, DDmd and DDyd can be derived from the above DDm and DDy:
Code: Select all   Expand viewCollapse view
`DDym =DATEDIF(\$A2;\$B2;"ym") =MOD(DDm;12)DDmd =DATEDIF(\$A2;\$B2;"md") =int(\$B2-date(year(\$A2);month(\$A2)+DDm;day(\$A2)))DDyd =DATEDIF(\$A2;\$B2;"yd") =int(\$B2-date(year(\$A2)+DDy;month(\$A2);day(\$A2)))`

Substituted for stand-alone use with start-date and end-date only:
Code: Select all   Expand viewCollapse view
`DDym =MOD(12*(YEAR(\$B2)-YEAR(\$A2))+MONTH(\$B2)-MONTH(\$A2)-(DAY(\$B2)<DAY(\$A2));12)DDmd =INT(\$B2-DATE(YEAR(\$A2);MONTH(\$A2)+12*(YEAR(\$B2)-YEAR(\$A2))+MONTH(\$B2)-MONTH(\$A2)-(DAY(\$B2)<DAY(\$A2));DAY(\$A2)))DDyd =INT(\$B2-DATE(YEAR(\$B2)-IF(MONTH(\$A2)>MONTH(\$B2);1;IF(MONTH(\$A2)=MONTH(\$B2);DAY(\$A2)>DAY(\$B2);0));MONTH(\$A2);DAY(\$A2)))`

 Edit: I left out functions DAYS, MONTHS and YEARS which are part of a Calc add-on and thus not compatible to other spreadsheets.
Attachments
datedif.xls
Replacements for Excel's undocumented function DATEDIF
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice

Villeroy
Volunteer

Posts: 29727
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany