My company uses Word VBA to create templates for our customer service letters. In one of them I use a Date Picker to input the start date of a policy, but also need to calculate the age of that policy in years. I have tried use:



b = Format(Me.DTPStartDate.Value, "d MMMM yyyy")
c = Format(Val(Date - Me.DTPStartDate.Value), "y")

Where c is the age of the policy

Any help would be much appreciated!

What kind of a Date Picker is this (e.g. content control, on a userform, etc.)? It might also help if you post a copy of the document (with the code - but remove anything sensitive). You can do this via the paperclip symbol on the 'Go Advanced' tab.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

The Date Picker is on a User Form.

Unfortunately, I am unable to post a copy of the document due to our company's IT polices.

Surely you can at least post a bare document containing a userform with whatever date-picker are you using? The standard Windows 7/Office 2010 set only includes the Calendar Control and I have no idea whether that's what you're referring to, something that's not supported in later Windows OSs (something you'll need to consider if anyone in your organisation uses Win 7 or 8), or a third-party product. Without knowing what you're actually using, so I can know what it returns, there's not much I can do to help ...

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

I'm unable to upload anything I'm afraid, however, I can confirm the Date Picker is the standard Date Picker used in Word 2010, it is the only one I am aware of and is selected from the toolbox (once I'd selected it from the additional commands).

I have tried another method to no avail...

The function you need is most likely DateDiff. The attached demo, using a Calendar Control, should get you started. Considerably more code would be needed to return the number of year months and month days.

Attached Files

DateDiff.doc (43.5 KB, 56 views)

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Thanks for your help macropod. Although I am now struggling to input the DateDiff function into the document! Just to clarify, I don't need it as a Message Box, but for it to become text to input into the documnet itself.

I have tried to do this by

Dim TheDate As Date
Dim AgeofPlan As Date
TheDate = DTPStartDate.Value
AgeofPlan = DateDiff(yyyy, TheDate, Now)

But keep getting a Run-time error '5': Invalid procedure call or argument on the last line.

You have:
Dim AgeofPlan As Date
but you're passing 'DateDiff(yyyy, TheDate, Now)' to it, which is a number, not a date.

To insert the content into the document, bookmark the destination, then use code like the attached.

Attached Files

DateDiff.doc (50.5 KB, 52 views)

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

The code in that attachment has gone straight over my head!

The destination is already bookmarked as 'Years1', I have no need for days or months being added, just the year (with no additional content), which will be inserted into the bookmark 'Years1' that is already in a sentence in the document, however, this is all dependent upon a series of logic arguments. Therefore, I need the age to be set as an expression in the same manner that I set the start date (b = Format(Me.DTPStartDate.Value, "d MMMM yyyy"), although in this case I want it to be "c").

All sorted, it would appear the issue I was having was that I didn't use quotation marks around the interval arguement in the DateDiff...

My apologises, bit of a schoolboy error!!!

D'oh, I thought I'd got this sorted, but unfortunately, the calculation appears to be giving out the wrong age

The line of code is:



Where

1. DTPStartDate is 22 January 1993
2. Date is 10 January 2014

Frustratingly the output is showing as 21, rather than 20.


Does anyone have any ideas?

That's because your second date is in the 21st year after the first date. Try using:

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Thank you that has corrected the code.