Mail Merge and GMT/UTC Dates

macropod · #2 05-05-2011, 04:30 PM

Hi Dev98,

This is possible, but the field coding to do so is complex:

Code:

{QUOTE{SET StartDate {MERGEFIELD UTCDate}}
{ASK Delay "What is the local UTC offset, in decimal hours (eg 2.5 or -11.5)?" \o}
{SET SumTime{=({StartDate \@ HH}+{Delay})*3600+{StartDate \@ mm}*60+{StartDate \@ ss}}}
{SET OffsetDay {=INT(SumTime/86400)-(SumTime<0)}}
{SET OffsetTime {=(SumTime<0)*86400+SumTime}}
{SET Hrs {=MOD(INT(OffsetTime/3600)+23,24)+1}}
{SET Min {=INT((OffsetTime-Hrs*3600)/60)}}
{SET Sec {=MOD(OffsetTime,60)}}
{SET a{=INT((14-{StartDate \@ M})/12)}}
{SET b{={StartDate \@ yyyy}+4800-a}}
{SET c{={StartDate \@ M}+12*a-3}}
{SET d{StartDate \@ d}}
{SET jd{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045+OffsetDay}}
{SET e{=INT((4*(jd+32044)+3)/146097)}}
{SET f{=jd+32044-INT(146097*e/4)}}
{SET g{=INT((4*f+3)/1461)}}
{SET h{=f-INT(1461*g/4)}}
{SET i{=INT((5*h+2)/153)}}
{SET dd{=h-INT((153*i+2)/5)+1}}
{SET mm{=i+3-12*INT(i/10)}}
{SET yy{=100*e+g-4800+INT(i/10)}}
{QUOTE "{dd}-{mm}-{yy} {Hrs}:{Min}:{Sec}" \@ "dddd, d MMMM yyyy' at 'HH:mm:ss"}}

Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste them from this message.

The above field code:
• assumes your date/time mergefield's name is 'UTCDate';
• prompts the user for the GMT/UTC offset; and
• outputs the adjusted date/time in "dddd, d MMMM yyyy at 'HH:mm:ss" format.

Change the mergefield's name to match your own. If you want to hard code the UTC/GMT offset, change the line:
{ASK Delay "What is the local UTC offset, in decimal hours (eg 2.5 or -11.5)?" \o}
to:
{SET Delay 5}
where the '5' represents the offset. Similarly, you can edit the adjusted date/time format to suit your needs.

Rather than trying to convert all the above into real fields manually, there's a macro I developed to do it that you can use, at: http://www.gmayor.com/export_field.htm#TextToField

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Saving INDV mail merges During the mail merge	sedain121	Mail Merge	2	10-04-2011 07:52 PM
mail merge	ericlch16	Mail Merge	3	04-30-2011 04:44 AM
Mail Merge	nonworduser	Mail Merge	0	08-24-2010 02:28 PM
help with a mail merge	akim281	Mail Merge	2	02-05-2010 02:54 PM
Mail Merge	Mohanss82	Mail Merge	0	01-21-2009 12:29 PM