A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Word » Mailmerge
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Calculating Dates



 
 
Thread Tools Display Modes
  #21  
Old July 1st, 2008, 09:50 PM posted to microsoft.public.word.mailmerge.fields
Peter Jamieson
external usenet poster
 
Posts: 4,550
Default Calculating Dates

I'm not sure there is a /really/ easy way to do this because AFAICS the user
will have to copy a "macropod" formula into every row of the table. Nor am I
completely sure what table layout you are aiming for.

But here's an approach that might be workable for simple tables with no
missing rows.

Let's suppose you have just two columns, A and B

In row 1 the user puts a date (say 29 Aug 2007) in column B, i.e. cell B1.
They also have to bookmark this date, not the cell containing it - let's say
the bookmark name is "mydate". The date has to be in a format that Word can
recognise and split correctly into day, month, year using date format
switches
In column A, row 2 and downwards, the user puts a number of days to add.
In every cell in column B, you put a copy of macropod's "Calculate a day,
date, month and year, using n days delay" fields with the following changes:
- everywhere he uses a DATE field, you change DATE to MYDATE
- change the { SET Delay 14 } field to

{ SET Delay{={QUOTE A{={SEQ R}+1}}}}

- personally I also have to change the sequence in the last field to
{yy}-{mm}-{dd} which I suspect works everywhere.

In essence in row 2 this is doing

{ SET Delay {=A2} }

and in a table { =A2 } is the value in cell A2, as long as it is a number.

It relies on the fact that you never use { SEQ R } except in this place in
this column. You might be better off "resetting the SEQ R sequence before
the first "macropod" field e.g. using { SEQ R \r1\h }
and then just using

{ SET Delay{={QUOTE A{SEQ R}}}}

There's a lot to go wrong in there!
--
Peter Jamieson
http://tips.pjmsn.me.uk

"Bob W" wrote in message
...
Macropod has the calculations perfect, but I need a way of making the
setup
of one of his amazing date field calculations, 'user-interactive'. Here's
what I'm trying to do:

A word .doc has a table that shows a proposed event schedule in this
format:
Event 1 (notification to proceed [ntp])
Event 2 (ntp+5 days)
Event 3 (ntp+12 days)
etc.

The goal is to let the user type in an initial NTP date in the first row,
and type in various numbers of days in the remaining rows, and then by
refreshing all the fields, the actual calendar dates for all the
subsequent
events are automagically calculated and appear in the table.

example of desired results:
Event 1 (notification to proceed [ntp]): January 1, 2009 typed by user)
Event 2 (ntp+5 days): January 6, 2009 (automatically calculated)
Event 3 (ntp+12 days) January 13, 2009 (automatically calculated)

Macropod's calcuation for this, (found in his downloadable DateCalc
document) is entitled, "calculate a day, month and year, using n days
delay".
It requires key values in his field calculation formula to be manually
modified by the 'programmer' - how do we make them user-interactive?

"Doug Robbins - Word MVP" wrote:
if you are a field masochist/afficionado like fellow MVP Macropod, you
can do it as shown at:

http://www.wopr.com/cgi-bin/w3t/show...?Number=249902



Ads
  #22  
Old July 1st, 2008, 10:29 PM posted to microsoft.public.word.mailmerge.fields
Bob W
external usenet poster
 
Posts: 88
Default Calculating Dates

You said it! but it just might work, with enough duct tape...

"Peter Jamieson" wrote:

There's a lot to go wrong in there!
--
Peter Jamieson
http://tips.pjmsn.me.uk


  #23  
Old July 2nd, 2008, 12:25 AM posted to microsoft.public.word.mailmerge.fields
macropod[_2_]
external usenet poster
 
Posts: 2,402
Default Calculating Dates

Hi Bob,

The date calculation example under the heading 'Date and Time Calculations in a Table' is already interactive, in that it solicits
both the start date (your ntp date) and the number of days to offset.

For your purposes, you apparently need the number of days to offset to be hard-coded, and you need two calculated results. To do
that, what I'd suggest doing is:
.. use the first field from example under the heading 'Date and Time Calculations in a Table' or, if you're setting your document up
as a form, use a date-formatted formfield, give it a bookmark name of 'StartDate' and set its properties to 'calculate on exit';
.. use the second and third fields from the example under the heading 'Calculate a Date Sequence' and:
. modify the second field by replacing all instances of 'DATE' with 'StartDate' and changing the 'SET Delay' field's value from
'1' to '5'; and
. modify the third copy by copying the 'SET Delay' field from the second field and changing it's value to '7'.

Cheers


"Bob W" wrote in message ...
Macropod has the calculations perfect, but I need a way of making the setup
of one of his amazing date field calculations, 'user-interactive'. Here's
what I'm trying to do:

A word .doc has a table that shows a proposed event schedule in this format:
Event 1 (notification to proceed [ntp])
Event 2 (ntp+5 days)
Event 3 (ntp+12 days)
etc.

The goal is to let the user type in an initial NTP date in the first row,
and type in various numbers of days in the remaining rows, and then by
refreshing all the fields, the actual calendar dates for all the subsequent
events are automagically calculated and appear in the table.

example of desired results:
Event 1 (notification to proceed [ntp]): January 1, 2009 typed by user)
Event 2 (ntp+5 days): January 6, 2009 (automatically calculated)
Event 3 (ntp+12 days) January 13, 2009 (automatically calculated)

Macropod's calcuation for this, (found in his downloadable DateCalc
document) is entitled, "calculate a day, month and year, using n days delay".
It requires key values in his field calculation formula to be manually
modified by the 'programmer' - how do we make them user-interactive?

"Doug Robbins - Word MVP" wrote:
if you are a field masochist/afficionado like fellow MVP Macropod, you
can do it as shown at:

http://www.wopr.com/cgi-bin/w3t/show...?Number=249902



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:24 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 OfficeFrustration.
The comments are property of their posters.