Hi Ajit, a) convert both the number in date format...
b) Substract the dates and divide by 365/366 as the case be. I hope that solves your problem. On 9/22/08, AJIT NAVRE <[EMAIL PROTECTED]> wrote: > > Use the formula, > > > =IF(A1>DATE(2004,3,31),0,IF(YEAR(DATE(2004,3,31))-YEAR(A1)=0,1,YEAR(DATE(2004,3,31))-YEAR(A1))) > > instead of yours. > > Regards, > > Ajit > > On Sun, Sep 21, 2008 at 12:26 PM, Balachander S <[EMAIL PROTECTED]> wrote: > >> Try >> a1 B1 c1 >> 1-Jan-04 31-Mar-04 0 2-Jan-04 1-Apr-05 1 2 >> >> [c1] =DATEDIF(A1, B1, "Y") >> >> >> On Sun, Sep 21, 2008 at 9:57 AM, vivek jain <[EMAIL PROTECTED]> wrote: >> >> >>> >>> >>> Dear All, >>> I am preparing a fixed asset register for the last fifty years for an >>> organization. I have to calculate the depreciation based on the no. of >>> years >>> an asset has been used. I have to calculate depreication upto 31/03/2004 >>> and then for the f.y. 2004-05. >>> Now I have to calculate the no. of years the asset has been used from >>> the >>> date of its acquisition to 31-03-2004. I have used the following formulae >>> for this:- >>> Say A1 is date of Acquisition (01/04/2000) >>> In B1 I have applied the following formulae >>> B1=2004-year(a1). >>> But the problem in this formula is that if an asset has been acquired on >>> 01/01/2004 it will show zero result whereas I want 1. >>> Again if the asset has been acquired on 01/01/2005 it will show "-1" >>> whereas >>> I want it to be zero. >>> Is there any other formula I can use. >>> >>> Thanks & regards... >>> >>> CA Vivek Jain, FCA >>> >>> >>> >>> >> >> >> > > > > -- > Thank You, > > Ajit Navre > > > > > --~--~---------~--~----~------------~-------~--~----~ Visit the blog to download Excel tutorials at http://www.excel-macros.blogspot.com To post to this group, send email to excel-macros@googlegroups.com For more options, visit this group at http://groups.google.com/group/excel-macros?hl=en Visit & Join Our Orkut Community at http://www.orkut.com/Community.aspx?cmm=22913620 Visit the blog to download Excel tutorials at http://www.excel-macros.blogspot.com To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com To see the Daily Excel Tips, Go to: http://exceldailytip.blogspot.com -~----------~----~----~----~------~----~------~--~---
