A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

11 months and 1 day from a given date



 
 
Thread Tools Display Modes
  #1  
Old April 23rd, 2010, 01:27 AM posted to microsoft.public.excel.worksheet.functions
PK
external usenet poster
 
Posts: 136
Default 11 months and 1 day from a given date

How can I calculate 11 months and 1 day from a given date using an excel 2003
function?
  #2  
Old April 23rd, 2010, 01:52 AM posted to microsoft.public.excel.worksheet.functions
Tige Brown
external usenet poster
 
Posts: 7
Default 11 months and 1 day from a given date

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?

  #3  
Old April 23rd, 2010, 01:53 AM posted to microsoft.public.excel.worksheet.functions
Rick Rothstein[_2_]
external usenet poster
 
Posts: 2,013
Default 11 months and 1 day from a given date

I presume weekends and holidays do not matter...

=DATE(YEAR(A1),MONTH(A1)+11,DAY(A1)+1)

--
Rick (MVP - Excel)



"pk" wrote in message
...
How can I calculate 11 months and 1 day from a given date using an excel
2003
function?


  #4  
Old April 23rd, 2010, 01:53 AM posted to microsoft.public.excel.worksheet.functions
Dave Peterson
external usenet poster
 
Posts: 19,791
Default 11 months and 1 day from a given date

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
  #5  
Old April 23rd, 2010, 07:37 AM posted to microsoft.public.excel.worksheet.functions
Joe User[_2_]
external usenet poster
 
Posts: 757
Default 11 months and 1 day from a given date

"pk" 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.

  #6  
Old April 26th, 2010, 04:01 PM posted to microsoft.public.excel.worksheet.functions
Ashish Mathur[_2_]
external usenet poster
 
Posts: 1,764
Default 11 months and 1 day from a given date

Hi,

You may try this. D5 has the date. E5 has 11. Please note that for the
EDATE function to work, you will have to install the Analysis Toolpak from
Tools Adins

=EDATE(D5,E5)+1

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"pk" wrote in message
...
How can I calculate 11 months and 1 day from a given date using an excel
2003
function?


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:30 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.