Macro to calculate and insert number of elapsed years?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

A company was founded in 1997. Is there a way to make number of years in
business, always current, in a field? i.e., embed a formula that knows
"1997" and can take today's date minus 1997 and insert the number "10" so
that desired boilerplate text says "We've been in business for XX years" and
the number of years will always be correct, year after year, whenever the
field is updated.
 
The following field construction should work:

{ = { DATE \@ "yyyy" } - 1997 }

The construction consists of 2 fields, one inside the other. The DATE field
returns the current year and 1997 is subtracted from that.

The field brackets must be inserted by pressing Ctrl+F9 (field brackets
cannot be typed using normal characters). Type the remaining part of the
field construction between the brackets. When finished, press F9 to update
the field (if this does not toggle field codes, press Alt+F9).

--
Regards
Lene Fredborg
DocTools - Denmark
www.thedoctools.com
Document automation - add-ins, macros and templates for Microsoft Word
 
Lene, Looks cool, but I'm getting a Syntax error even when I follow your
procedure and copy/paste your formula (from the = sign to 1977). What am I
doing wrong?
 
I tried pressing Ctrl+F9 twice to generate the double brackets, then
carefully inserting the rest of the contents...this produces the result,
"2007 -1997" instead of the desired, "10". Getting closer, but not quite
there, yet!
 
OK, got it at last; i just wasn't being careful enough with the pesky syntax.
 
I am glad to hear it works now. I did not see your posts until you had solved
the problems.

--
Regards
Lene Fredborg
DocTools - Denmark
www.thedoctools.com
Document automation - add-ins, macros and templates for Microsoft Word
 
This is very handy. For years I've seen people embedding hard dates into
corporate documents, which are doomed to become outdated, requiring manual
re-editing that almost never happens because everyone's too busy. This
little gem solves that problem! THANKS!!
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

self-updating year reference brain teaser 5
Complex Time Window / date based calculation 1
How to find and replace text in a document with changing variables 2
calculate time elapsed inbetween days 3
Unexpected result in date calculations 6
Excel 2007 - calculating % of a year that has elapsed 5
Concatenate Date and Number field for Append Query 0
How to enter dates in Excel cells using InputBox 2

Back
Top