Automatically filling in a field depending on others 1

[Navvy]

I have a spreadsheet which looks as follows:

Finish Date Begin Date No. of Months
07-Mar-02 07-Mar-01 12M
09-Apr-02 09-Oct-01 6M
05-Feb-04 05-Feb-02 2Y
24-Feb-03 22-Feb-02 12M

I would like the "No. of Months" field filled in automatically as soon as "Finish Date" and "Begin Date" is filled. Is this possible?

It would need to round up to the nearest number of weeks or months or years, e.g. in the last row where 24-Feb-03 ---> 22-Feb-02 is not exactly 12 months.

I'm very new to VBA so need as much help as possible with regards to coding.

Many thanks in advance.

 

[SkipVought]

Hi,

If you are not concerned with leapyears and the such then this formula will work on the spreadsheet...

=INT((Finish_Date-Begin_Date)/(365/12))

Hope this helps Skip,
metzgsk@voughtaircraft.com

 

[SkipVought]

Hi,

If you are not concerned with leapyears and the such then this formula will work on the spreadsheet...

=INT((Finish_Date-Begin_Date)/(365/12))

=INT((A2-B2)/(365/12))

Hope this helps Skip,
metzgsk@voughtaircraft.com

 

[Navvy]

Skip to the rescue again! <Insert Star>!

Another quick question. I don't necessarily put the data in A2 or B2 as I am continuously deleting and inserting rows. So, I presume I have to do some coding around this.

Can you help?

 

[SkipVought]

Navvy,

1. Insert the formula anywhere in row 2 (other than Col A or B) and then COPY it to any other row(s)

2. You ought to register at tek-tips. That way you COULD give any worthy contributor a STAR and reap the benefits of on-going participation.

Skip,
metzgsk@voughtaircraft.com