Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Mailmerge and Fax / May 2004

Tip: Looking for answers? Try searching our database.

Need to calculate a number based on a date

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Tutu B - 19 May 2004 20:33 GMT
I need help calculating a number in a Word 2003 mail
merge.  I have a date for when an individual starts
working, I need to merge the date and have it give me how
many years they have worked.
macropod - 20 May 2004 06:53 GMT
Hi Tutu B,

Two ways:

1) You could use a macro like:

Sub CalcTerm()
Dim StartDate As Date, EndDate As Date, Include As String
Dim Years As Integer, Months As Integer, Days As Integer, DaysInMonth As
Integer
On Error GoTo ExitSub
StartDate = InputBox("Enter the Start Date in dd/mm/yyyy format")
EndDate = InputBox("Enter the End Date in dd/mm/yyyy format")
Include = UCase(Left(InputBox("Include both the Start and End Dates?
(Y/N)"), 1))
If (Month(EndDate) = 2) Then
   DaysInMonth = 28 + (Month(EndDate) = 2) * ((Year(EndDate) Mod 4 = 0) +
(Year(EndDate) Mod 400 = 0) - (Year(EndDate) Mod 100 = 0))
Else
   DaysInMonth = 31 - (Month(EndDate) = 4) - (Month(EndDate) = 6) -
(Month(EndDate) = 9) - (Month(EndDate) = 11)
End If
Years = Year(EndDate) - Year(StartDate) + (Month(EndDate) <
Month(StartDate)) + (Month(EndDate) = Month(StartDate)) * (Day(EndDate) <
Day(StartDate) + (Include = "Y"))
Months = (12 + Month(EndDate) - Month(StartDate) + (Day(EndDate) <
Day(StartDate) + (Include = "Y"))) Mod 12
Days = (DaysInMonth + Day(EndDate) - Day(StartDate) - (Include = "Y")) Mod
DaysInMonth
MsgBox "The term is " & Years & " year(s) " & Months & " month(s) " & Days &
" day(s)."
ExitSub:
End Sub

2) Do the calculation with a Word field. You can download a Word document
with fields coded to do this and more with dates from:
http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=wrd&Number=249902
(url all one line)

In either case, modify to suit your needs

Cheers

> I need help calculating a number in a Word 2003 mail
> merge.  I have a date for when an individual starts
> working, I need to merge the date and have it give me how
> many years they have worked.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.