• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to add months in a common ending date [SOLVED]

V

VDS

Member
@Dear All,

I attaching herewith a sample file in which the next month is to be added in a sequence.
Column A4 contains list of RA bills and Column B4 contains the date.(upto a particular period).
If Column A5 is the next RA Bill, Column B4 becomes the common ending date. Here, year and date may or may not be same. But month will be next month. Basically this is required to do the monthly analysis. How to do this.


VDS
 

Attachments

Hi Santhosh ,

To get a date which is exactly one month from a given date , you can use the EDATE function ; if you are using Excel 2003 , this is available only if you install the Analysis Toolpak.

Thus , if B2 contains 30.04.2013 , then =EDATE(B2,1) will return 30.05.2013 ; you should be careful with adding one month if the date is January end , since =EDATE(B2,1) where B2 contains 30.01.2013 will return 28.02.2013 ; thereafter , using EDATE on this will return the 28th of every month.

Narayan
 
Hi Santhosh ,

What you can use is :

=EOMONTH(EOMONTH(B2,0)+1,0)

where the EOMONTH function is also from the Analysis Toolpak.

Narayan
 
@Narayan Sir,

Thank U so much. Doubt is clear. Small doubt, =Edate(B2,1) will result next one month. Is the duration of month can be extended like 2 or 3, etc ?. It may be helpful little bit while making quarter wise reports in Accounts.


VDS
 
Hi Santhosh ,

Sure ; the second parameter to the EDATE function can be both positive and negative numbers , so that a positive number returns a date after the reference date , while a negative number returns a date before the reference date.

Thus , if B2 contains 30.06.2013 , then :

=EDATE(B2,7)

will return 30.01.2014

Similarly , for the same value of B2 :

=EDATE(B2,-11)

will return 30.07.2012

You can even use decimal numbers , but Excel will use only the integer portion ; thus =EDATE(B2,-11.73) will give the same result as =EDATE(B2,-11)

Narayan
 
  • Like
Reactions: VDS
@Narayan sir,

You deserve so many salutes as you have given me so much valuable info and support.


VDS
 
@VDS

Just as an advise, try to keep sample relevant to your problem in sample file & if your file has multiple sheets try to give sheet ref. in your problem statement.

Regards,
 
@Somendra,

Surely. The query was only to show how to add the months. It is now solved. Thanks for your support too.


VDS
 
You must log in or register to reply here.
Back
Top