Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-17-2013, 08:45 AM
BoringDavid BoringDavid is offline Calculating age of something from a Date Picker Windows XP Calculating age of something from a Date Picker Office 2010 32bit
Novice
Calculating age of something from a Date Picker
 
Join Date: Sep 2013
Posts: 19
BoringDavid is on a distinguished road
Default Calculating age of something from a Date Picker

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!
Reply With Quote
  #2  
Old 10-17-2013, 04:27 PM
macropod's Avatar
macropod macropod is offline Calculating age of something from a Date Picker Windows 7 32bit Calculating age of something from a Date Picker Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #3  
Old 10-18-2013, 01:29 AM
BoringDavid BoringDavid is offline Calculating age of something from a Date Picker Windows XP Calculating age of something from a Date Picker Office 2010 32bit
Novice
Calculating age of something from a Date Picker
 
Join Date: Sep 2013
Posts: 19
BoringDavid is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 10-18-2013, 02:02 AM
macropod's Avatar
macropod macropod is offline Calculating age of something from a Date Picker Windows 7 32bit Calculating age of something from a Date Picker Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #5  
Old 10-18-2013, 02:20 AM
BoringDavid BoringDavid is offline Calculating age of something from a Date Picker Windows XP Calculating age of something from a Date Picker Office 2010 32bit
Novice
Calculating age of something from a Date Picker
 
Join Date: Sep 2013
Posts: 19
BoringDavid is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
Surely you can at least post a bare document containing a userform with whatever date-picker are you using?
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...

Quote:
Dim StartDate, StartYear
StartDate = Me.DTPStartDate
StartYear = Year(StartDate)

Dim TodayDate, TodayYear
TodayDate = Date
TodayYear = Year(TodayDate)

c = DateAdd(yyyy, -StartYear, TodayYear)
Reply With Quote
  #6  
Old 10-18-2013, 03:02 AM
macropod's Avatar
macropod macropod is offline Calculating age of something from a Date Picker Windows 7 32bit Calculating age of something from a Date Picker Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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
File Type: doc DateDiff.doc (43.5 KB, 52 views)
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 10-18-2013, 04:04 AM
BoringDavid BoringDavid is offline Calculating age of something from a Date Picker Windows XP Calculating age of something from a Date Picker Office 2010 32bit
Novice
Calculating age of something from a Date Picker
 
Join Date: Sep 2013
Posts: 19
BoringDavid is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
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.
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.
Reply With Quote
  #8  
Old 10-18-2013, 05:18 AM
macropod's Avatar
macropod macropod is offline Calculating age of something from a Date Picker Windows 7 32bit Calculating age of something from a Date Picker Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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
File Type: doc DateDiff.doc (50.5 KB, 50 views)
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #9  
Old 10-18-2013, 06:47 AM
BoringDavid BoringDavid is offline Calculating age of something from a Date Picker Windows XP Calculating age of something from a Date Picker Office 2010 32bit
Novice
Calculating age of something from a Date Picker
 
Join Date: Sep 2013
Posts: 19
BoringDavid is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
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.
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").
Reply With Quote
  #10  
Old 10-18-2013, 07:28 AM
BoringDavid BoringDavid is offline Calculating age of something from a Date Picker Windows XP Calculating age of something from a Date Picker Office 2010 32bit
Novice
Calculating age of something from a Date Picker
 
Join Date: Sep 2013
Posts: 19
BoringDavid is on a distinguished road
Default

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!!!
Reply With Quote
  #11  
Old 01-10-2014, 05:29 AM
BoringDavid BoringDavid is offline Calculating age of something from a Date Picker Windows XP Calculating age of something from a Date Picker Office 2010 32bit
Novice
Calculating age of something from a Date Picker
 
Join Date: Sep 2013
Posts: 19
BoringDavid is on a distinguished road
Default

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:

Code:
c = DateDiff("yyyy", DTPStartDate.Value, Date)


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?
Reply With Quote
  #12  
Old 01-10-2014, 03:02 PM
macropod's Avatar
macropod macropod is offline Calculating age of something from a Date Picker Windows 7 32bit Calculating age of something from a Date Picker Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

That's because your second date is in the 21st year after the first date. Try using:
Code:
c = DateDiff("yyyy", DTPStartDate.Value, Date) - 1
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #13  
Old 01-13-2014, 02:40 AM
BoringDavid BoringDavid is offline Calculating age of something from a Date Picker Windows XP Calculating age of something from a Date Picker Office 2010 32bit
Novice
Calculating age of something from a Date Picker
 
Join Date: Sep 2013
Posts: 19
BoringDavid is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
That's because your second date is in the 21st year after the first date. Try using:
Code:
c = DateDiff("yyyy", DTPStartDate.Value, Date) - 1
Thank you that has corrected the code.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Default dates for a Date Picker BoringDavid Word VBA 2 09-11-2013 01:42 AM
Calculating age of something from a Date Picker Date Picker Andy2011 Word VBA 4 11-24-2012 10:07 PM
Calculating age of something from a Date Picker word 2003 date picker nashville Word 16 04-06-2012 04:12 AM
Date picker trintukaz Excel 0 12-30-2011 12:42 AM
Calculating age of something from a Date Picker Calculating age at date jamierbooth Excel 2 02-22-2011 03:35 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:55 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft