VBA Q

[dipsy]

Hello!

I have a column that indicates project end dates.
I want to use VBA to do get the number of months remaining
in the year from the end date. The end date is in the
form: 03 Oct 2003

Thanks in advance for the help.

 

[Randall Roberts]

You dont really need VBA to do this you can use a formula such as

=DATEDIF(A1,"12/31/03","m")

would give you the months left in the year from the date in A1

=DATEDIF(A1,"12/31/03","ym")&" Months and
"&DATEDIF(A1,"12/31/03","md")&" Days"

Would give you the months and days left

Randall

 

[dipsy]

It did not work. Also, I would like to incorporate this
in my code.

 

[Jim]

One way:
Put this in This Workbook Workbook_Open event:

Dim MyDate As Integer
MyDate = DateDiff("m", Date, #1/3/2004#)
MsgBox MyDate & " months until the big day!"

If the date has passed, as in case you posted, it should be entered first.

You should know that months are open to interpretation. Some people say
"months" and mean 30-day periods, others mean actual months, such as January
or February. Others consider this a meaningless value, since it does not
account for partial months; the actual answer could be 6 months and 29 days,
and would show up as 6 months. You may make a nested statement to return the
days that constitute a partial month, if needed.

 

[Jim]

Note that the VBA is DATEDIFF, the WS formula is DATEDIF.

 

[dipsy]

Cells(i, 7) = DateDiff(Cells(i, 8), "12/31/2003", "m")

gives a type mismatch error.

Note, I have set the format in cells(i,8)as "mm/dd/yyyy".

Thanks a ton!