On Tue, 22 Apr 2008 06:58:28 +0100 Brian Barker <[EMAIL PROTECTED]> wrote:
> At 13:08 21/04/2008 -0400, Jerry Feldman wrote: > >I routinely take Excel spreadsheets and convert them to Calc. The > >spreadsheets are functional specifications for a product I am > >working on. The problem is there are a number of columns that use > >date calculations that require the Excel DATEDIF() function. > >OpenOffice.org have roughly functions such as YEARS(). The ODF > >specification (ISO/IEC 26300:2006 Open Document Format for Office > >Applications (OpenDocument) v1.0) does define the DATEDIF() function > >AFAIK. Actually, it is a minor pain to convert these as the total > >time is about 5 mins a couple of times a month. Actually, GNUMeric > >support datedif() but GNUMeric is not supported on RHEL 4 Update 3 > >that I am required to run. One of the big benefits for the spec > >writer using Excel and me using OO.o is that it catches some errors. > >The person writing the spec could use OO.o since it is installed on > >her Windows laptop, but others in the company only use Excel so we > >do need to maintain the document in MS .xls format. > > I'm not an expert on Excel and don't even have it available to test, > but I suspect that you may be going about this the wrong way. The > Microsoft web site says that the DATEDIF() function in Excel is > provided for compatibility with Lotus 1-2-3. I interpret this to > mean that Excel has other facilities that provide all that is > necessary and that you shouldn't need to use DATEDIF() if Lotus 1-2-3 > is not part of your picture. Is it possible that by using these > other techniques in Excel your work would transfer more effectively > to and fro between Excel and Calc? > > Note that you may not need a function to calculate the difference > between two dates: it is obtained simply by subtracting them. You > may then be able to treat the result appropriately (using standard > functions?) to extract the precise information you need (which is not > clear above). There is no other way in Excel to do what needs to be done. As an example, put a date of birth in one cell (say 3/4/1957 as in my spec in a1), then put a column of dates starting at 7/4/2007 (starting at b1) and incrementing one month down to 6/4/2056 =DATE(YEAR(B1);MONTH(B1)+1;DAY(B1)). Then add an age column. In OO.o =YEARS($A$1;$B1;0);""). You will find that using the OO.o YEARS() function works correctly where the age increments every March. Using a mathematical formula (dividing by 365.25), you will find that towards the end, the age increment moves to April in 2011. The only function in Excel that allows me to do this accurately is the DATEDIF() function. -- -- Jerry Feldman <[EMAIL PROTECTED]> Boston Linux and Unix PGP key id: 537C5846 PGP Key fingerprint: 3D1B 8377 A3C0 A5F2 ECBB CA3B 4607 4319 537C 5846
signature.asc
Description: PGP signature
