Calculations in merge

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BilboBaggins - 28 Sep 2004 10:56 GMT

Good morning
I have a personnel database which produces a simple table
of mailmerge information that I cannot change. I want to
do some calculations with the information and will have to
do them in the document (calculated field?) Part of the
information produced is a start date and an annual holiday
entitlement. I want to do a calculation taking the month
number of the start date, subtracting that from 12, then
dividing this by 12 and multiplying the result by the
annual holiday entitlement. The result then needs to be
rounded up to the nearest 0.5. Example: start date 12
September (month 9): 12 - 9 = 3, /12 = 0.25, x20 days
holiday for full year = 5 days pro rata for the remainder
of this year.
Not sure if this is possible - any suggestions most
welcome.

Reply to this Message

Graham Mayor - 28 Sep 2004 11:08 GMT

Calculations on date fields are *very* complicated, but not impossible - see
http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=wrd&Number=249902

Signature

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

> Good morning
> I have a personnel database which produces a simple table
[quoted text clipped - 12 lines]
> Not sure if this is possible - any suggestions most
> welcome.

Reply to this Message

macropod - 30 Sep 2004 15:14 GMT

Hi Graham,

Given that only whole months are being used, I doubt that anything so
elaborate would be needed in this case.

The following should do:
{=INT((1-{MERGEFIELD sd \@ M}/12)*{MERGEFIELD en}*2+(MOD((1-{MERGEFIELD sd
\@ M}/12)*{MERGEFIELD en},1)<>0))/2 \# 0.00}

Cheers
PS: This corrects a flaw in my previous post, which gave the same
(incorrect) answers as Peter's.

> Calculations on date fields are *very* complicated, but not impossible - see
> http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=wrd&Number=249902
[quoted text clipped - 15 lines]
> > Not sure if this is possible - any suggestions most
> > welcome.

Reply to this Message

Peter Jamieson - 29 Sep 2004 15:25 GMT

You need the { = } field, and Let's say your start date is a field in the
data source called "sd" and your entitlement is called "en". Then you could
try

{ = int(((((12 - { MERGEFIELD sd \@M })/12)*{ MERGEFIELD en })+0.25)*2)/2 }

where all the {} are the special field code braces you can insert using
ctrl-F9.

I /think/ the rounding is correct for what you want but you'll need to check
for yourself.

Signature

Peter Jamieson

> Good morning
> I have a personnel database which produces a simple table
[quoted text clipped - 12 lines]
> Not sure if this is possible - any suggestions most
> welcome.

Reply to this Message

macropod - 30 Sep 2004 08:46 GMT

Hi,

I believe you can also get the required result with the shorter:
{=INT((1-{MERGEFIELD sd \@ M}/12)*{MERGEFIELD en}*2)/2}

If you want to force the result to always display the value after the
decimal point, add a numeric picture switch, thus:
{=INT((1-{MERGEFIELD sd \@ M}/12)*{MERGEFIELD en}*2)/2 \# 0.0}

Cheers

> Good morning
> I have a personnel database which produces a simple table
[quoted text clipped - 12 lines]
> Not sure if this is possible - any suggestions most
> welcome.

Reply to this Message

Rate this thread: