[Solved] Replacing DATEDIF in an Excel equation?

Discuss the spreadsheet application

[Solved] Replacing DATEDIF in an Excel equation?

Postby mrdelurk » Thu Aug 14, 2008 10:47 pm

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?

Postby Villeroy » Thu Aug 14, 2008 11:00 pm

=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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26983
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Replacing DATEDIF in an Excel equation?

Postby keme » Thu Aug 14, 2008 11:09 pm

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
User avatar
keme
Volunteer
 
Posts: 3216
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Replacing DATEDIF in an Excel equation?

Postby Villeroy » Thu Aug 14, 2008 11:36 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26983
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Replacing DATEDIF in an Excel equation?

Postby mrdelurk » Fri Aug 15, 2008 3:01 am

Thank you for your help,

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?

Postby Villeroy » Sat Aug 23, 2008 11:28 am

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
(367 KiB) Downloaded 1176 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26983
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 26 guests