11 months and 1 day from a given date
in [Functions]
|
From: pk on 22 Apr 2010 20:27 How can I calculate 11 months and 1 day from a given date using an excel 2003 function?
From: Tige Brown on 22 Apr 2010 20:52 Presuming the date is in cell A1; =date(year(a1),month(a1)+11,day(a1)+1) "pk" wrote: > How can I calculate 11 months and 1 day from a given date using an excel 2003 > function?
From: Rick Rothstein on 22 Apr 2010 20:53 I presume weekends and holidays do not matter... =DATE(YEAR(A1),MONTH(A1)+11,DAY(A1)+1) -- Rick (MVP - Excel) "pk" <pk(a)discussions.microsoft.com> wrote in message news:9820FCC2-9414-4A5C-B168-E9B8B82E6848(a)microsoft.com... > How can I calculate 11 months and 1 day from a given date using an excel > 2003 > function?
From: Dave Peterson on 22 Apr 2010 20:53 In the future? With the date in A1: =date(year(a1),month(a1)+11,day(a1)+1) pk wrote: > > How can I calculate 11 months and 1 day from a given date using an excel 2003 > function? -- Dave Peterson
From: Joe User on 23 Apr 2010 02:37
"pk" <pk(a)discussions.microsoft.com> wrote: > How can I calculate 11 months and 1 day from a given date using > an excel 2003 function? That depends. Which dates would you prefer for 11 months after the following dates on the left? 3/31/2010 + 11mo = (a) 3/3/2011 or (b) 2/28/2011 5/31/2010 + 11mo = (a) 5/1/2011 or (b) 4/30/2011 7/31/2010 + 11mo = (a) 7/1/2011 or (b) 6/30/2011 10/31/2010 + 11mo = (a) 10/1/2011 or (b) 9/30/2011 12/31/2010 + 11mo = (a) 12/1/2011 or (b) 11/30/2011 Column (a) is the result of DATE(YEAR(A1),11+MONTH(A1),DAY(A1). Column (b) is the result of EDATE(A1,11). For most purposes, people prefer (b). On the other hand, for your purposes, would it bother you that with EDATE, 11mo plus 3/28/2010, 3/29/2010 and 3/30/2010 as well as 3/31/2010 are all 2/28/2011? That does follow US law for most purposes. If you like column (b), then 11mo plus 1day is simply 1+EDATE(A1,11). You might need to select the Date format explicitly after entering or editing the formula. If you get a #NAME error, see the EDATE help page for the remedy. |