Counting months up to a certain date

I am working on a breif in which we are trying to find out our tax liability with regard to contractors.

i have a column containing dates in the following format 16/05/07

I need is a bit of coding to be able to count how many months have passed up to the 31st march 2008 (31/03/08).

What i am after is to have is the number of months till the 31st march 2008 and then multiply this by 19 (average number of days worked accross a month).

can anyone help with a bit of code or put me in the right direction.

Thanks in advance

Do you need to count the number of whole months elapsed or the number of months and fractions of months ?

Originally Posted by arthurbr

Do you need to count the number of whole months elapsed or the number of months and fractions of months ?

whole months please, but i would be interested in how you would work out the fractions of months. Im sure ill use that down the line too.

:-)

If you put the end date, 31st March 2008, in a cell, e.g. D1 and your dates are in A2 down then in B2 copied down this formula would give the number of elapsed whole months

=DATEDIF(A2,D$1,"m")

So you can just multiply by 19 like

=DATEDIF(A2,D$1,"m")*19

or just calculate the number of days between the dates and divide by 30.5 (approx average number of days in a month) and multiply by 19, i.e.

=(D$1-A2)*19/30.5

For complete months use the DATEDIF function =

Please Login or Register  to view this content.

You can get more info here :http://www.cpearson.com/excel/datedif.aspx

Cheers

thats a great help guys but for some reason im not getting the right result.

ill show you exactly whats happening

column O has my start dates

i have put the date of the 31st march in column AJ

in colum AK i have the following code =DATEDIF(O17,AJ17,"m")

this is the return i am getting
08/01/00


what have i done wrong????

You should format the result cell as number. That will do the trick

Originally Posted by arthurbr

You should format the result cell as number. That will do the trick

(wipes egg off his face), thanks so much. and thanks to daddylonglegs for the coding too.

i used the 30.5 days and the whole months, was a real eye opener as both are correct yet the difference works out to be just shy of £100,000.

as always the books can be cooked in all sorts of ways.