Formula Field Date + 28 days 1

[DJSmith]

Hi Guys,

I need to add a field onto a mail merge document that displays the current date + 28 days. I can write the code for this no worries in a macro (VBA) but i'm not sure how I should be adding it onto each document (This needs to be done automatically when the document is opend and merged).

Is there any way I can create a Formula field to run the macro and display the result??

Thanks

DJ.

 

[TonyJollans]

Hi DJSmith,

Word Fields are only for the very sad

This should give you the date 28 days hence - braces all entered via Ctrl+F9 - I've tried to lay it out so it makes sense ..

[blue][tt]{ Quote { Set Year { Date \@ yyyy } }
{ Set Leap { =(MOD(Year,4)=0) - (MOD(Year,100)=0) + (MOD(Year,400)=0) } }
{ Set Month { Date \@ M } }
{ Set EoMonth { =IF(Month=2, 28+Leap, 31-MAX(Month=4,Month=6,Month=9,Month=11)) } }
{ Set Day { ={ Date \@ dd } + 28 } }
{ Set Month { =Month + (Day>EoMonth) } }
{ Set Day { =IF(Day>EoMonth, Day-EoMonth, Day) } }
{ Set Year { =Year + (Month=13) } }
{ Set Month { =IF(Month=13, 1, Month) } }
{ Set NewDate { =(Year*10000) + (Month*100) + Day } }
{ ={ NewDate } \# "0000/00/00" } \@ "dd MMMM yyyy" }[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[macropod]

Hi DJ,

Doing this with field coding is fairly complex, but the hard works all be done for you! Download the date calculations document I created at:

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

and check out the 'Calculate a day, date, month and year, using n (301) days delay' topic. For the question you posed, simply copy the example there and change the 'SET Delay 301' parameter in the field to 'SET Delay 28'.

The file also has an alternative version, for use where a starting date is obtained from the mailmerge source file (see the topic 'Date Calculations In A Mailmerge').

Cheers
PS: Tony's version looks like it will work reliably for offsets of up to +28 days, but you might run into problems after that. Mine can both add and subtract pretty much any number of days you're likely to need.