[Solved] Replacing DATEDIF in an Excel equation?

[mrdelurk]

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

[Villeroy]

=NOW()-A4 returns the difference between now and a time in A4 since all spreadsheets calculate times as numbers in unit "days".

[keme]

=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().

[Villeroy]

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

[mrdelurk]

Thank you for your help,

I replaced the equation with

=TODAY()-A4

and now it works in both programs.

[Villeroy]

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

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

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

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.