Calculations in merge

[BilboBaggins]

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.

 

[Graham Mayor]

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

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


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Peter Jamieson]

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.

 

[macropod]

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

 

[macropod]

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.