Mail Merge for non-Gregorian Calendars

[James]

Hi

The field feature in Word has an option that supports non-Gregorian
calendars, like the Saka Era calendar and the Hijri/Lunar calendar. For
example, the field code for today's date for the Saka Era calendar is:
{DATE \s \* MERGEFORMAT‎}
for the Hijri/Lunar calendar it's:
{DATE \h \* MERGEFORMAT‎}

The problem is with mail merge: I can't find the correct formula to show
dates in Word in any of those two non-Gregorian calendars (the data source
for the mail merge is an Excel file that contains a column of dates). Word
keeps showing the Gregorian format. PS: As a test, I converted the dates in
Excel into the hijri format and then used the "via DDE" option when opening
the data source. Still, dates showed in Gregorian format in Word's mail merge.

Is there any way to show dates in any of those two non-Gregorian formats in
Word's mail merge?

 

[Peter Jamieson]

Yes, you are correct that the \s and \h fields are for use with Word's
DATE type fields only (DATE, PRINTDATE etc.). FWIW when Word insertsthe
data for a { MERGEFIELD } field, it is already in text format ( e.g.
"01/12/2008" or "2008-12-01" and Word field codes themselves provide no
out-of-the-box translation of the year, month and day into other data
systems.

Which means that you either have to fix those dates in Word using
another method, or fix them in the data source.

As you say, in this caae, using Excel to format the dates as (e.g.)
Hijri makes no difference to the date that Word sees, even with a DDE
connection. Unfortunately, it's not all that easy to fix that in Excel
using worksheet functions either, because, e.g. if you try to use
LEFT(), MID() functions to extract the year, month and day, they
actually try to operate on the underlying Excel date number. However, I
can't say I've explored that option in Excel completely yet.

Typically, another approach is to copy/paste the data from Excel into
Word, then use that as the data source. However, in that case, be aware
that you will need to get your date data in Excel into exactly the
format you need, because of another really nasty limitation of Word,
which is that it will not recognise a date with a year earlier than 1901
as a date - e.g. even 1428-12-23 will not be recognised as a date so you
cannot reformat it using e.g. \@MM/DD/YYYY.

There are various other possible ways to approach this. I am sure
macropod's date calculation fields (search this newsgroup for the
appropriate link) can be adapted to deal with Hijri/Saka, although I'm
not going to attempt that myself, at least not right now. Similar
algorithms can be used either in Excel VBA or Word VBA to do
Excel->Hijri/Saka conversions. Hijri is particularly straightforward
because there is some support in VBA - e.g. you can use VBA to create a
user-defined Excel Worksheet function like:

Function dtoh(mydate) As String
Dim valCal as VBA.VbCalendar
valCal = VBA.Calendar
VBA.Calendar = vbCalHijri
dtoh = Format(mydate, "DD/MM/YYYY")
VBA.Calendar = valCal
End Function

Then you can create a new column in your sheet and use

=dtoh(B2) to convert a date in cell B2 etc.

For Saka you'd need to do a more complex calculation which I don't have
the time to attack right now (however, it may be simplified by the fact
that Saka leap years are the same as Gregorian leap years)

Best I can do right now...

Peter Jamieson

http://tips.pjmsn.me.uk

 

[James]

Thanks a lot Peter, you did more than enough.

While the DDE method didn't work with Excel, it amazingly worked with Access
(but showing the dates in their short formats only). Maybe the best method
for now, and the easiest for my users, is the copy/paste. Based on that, some
of them don't need the date format in Excel for their dates. So it was
suggested that they copy the dates column, paste it into Word, change the
dates column in Excel into text format, and then paste the Word dates column
into it again. However, others were given Access files to work their mail
merge based on them. And they can't do the trick with these Access files
because the dates are used in some calculations.

Thanks again