Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Mail Merge for non-Gregorian Calendars
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? |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Mail Merge for non-Gregorian Calendars
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 wrote: 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? |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Mail Merge for non-Gregorian Calendars
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 "Peter Jamieson" wrote: 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 wrote: 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? |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calendars | Tables | |||
Mail-Merge: Can we have master detail relationship or multiple entities in word Mail-Merge? | Microsoft Word Help | |||
Calendars | Microsoft Word Help | |||
Calendars | Microsoft Word Help | |||
Can I copy info directly from 2006 calendars to 2007 calendars? | Microsoft Word Help |