[MAIL MERGE WORD] Date field calculation: display year + 1
julolamayo
Posted messages
5
Registration date
Status
Membre
Last intervention
-
NifNEf -
NifNEf -
Hello everyone,
I am working on merging a Word document from fields obtained from a management software. The tables are therefore absolutely non-modifiable.
The Word file must be uploaded to the software, and then the merge is performed on a common remote computer where Word is installed. The merged file obtained locally is a PDF file.
I am trying to get the calculation: "year + 1" on my merged document using the formula:
{ = 1 + { MERGEFIELD CER2_DATEH \@ "yyyy" \# 0000 } }
I would like to clarify that:
- the brackets were created with Ctrl + F9,
- the date field alone works, with the result of "{ MERGEFIELD CER2_DATEH \@ "yyyy" }" = "2021",
- a calculation on numbers alone also works, for example "{ = 1 + 1 }" correctly returns "2".
I suspect the problem is that Word interprets the year result as text, which is why I added "\# 0000," which seems to be insufficient. I have been trying for hours in vain to obtain the desired result (display "2022"), thank you for your help.
Configuration: Windows / Chrome 92.0.4515.107
I am working on merging a Word document from fields obtained from a management software. The tables are therefore absolutely non-modifiable.
The Word file must be uploaded to the software, and then the merge is performed on a common remote computer where Word is installed. The merged file obtained locally is a PDF file.
I am trying to get the calculation: "year + 1" on my merged document using the formula:
{ = 1 + { MERGEFIELD CER2_DATEH \@ "yyyy" \# 0000 } }
I would like to clarify that:
- the brackets were created with Ctrl + F9,
- the date field alone works, with the result of "{ MERGEFIELD CER2_DATEH \@ "yyyy" }" = "2021",
- a calculation on numbers alone also works, for example "{ = 1 + 1 }" correctly returns "2".
I suspect the problem is that Word interprets the year result as text, which is why I added "\# 0000," which seems to be insufficient. I have been trying for hours in vain to obtain the desired result (display "2022"), thank you for your help.
Configuration: Windows / Chrome 92.0.4515.107
4 réponses
Hello,
The matter is reported, according to sources, as being unreliable, very difficult, if not impossible.
However, one author claims to have found a formula:
Pretty much everyone agrees that there is no sound solution outside of macros, see for example here, datecalc.zip, while others recommend third-party utilities:
http://www.gmayor.com/downloads.htm
The matter is reported, according to sources, as being unreliable, very difficult, if not impossible.
However, one author claims to have found a formula:
{={MERGEFIELD "MADATE"\@d}} / {={MERGEFIELD "MADATE"\@MM}}/ {=10+{MERGEFIELD "MADATE"\@yyyy}}
Pretty much everyone agrees that there is no sound solution outside of macros, see for example here, datecalc.zip, while others recommend third-party utilities:
http://www.gmayor.com/downloads.htm
Hello and thank you for your responses,
This is software where you load a model in Word format, which allows you to automatically complete administrative documents based on data entered into this software. You can then initiate printing, and the merge does not occur on the workstation, but within the software. The output data is therefore a directly printable PDF file. What I mean is that it is not possible to make any adjustments in Word, as the merge is likely performed on a remote computer through the software. The only modifications I can make are within the Word file, and only that.
All fields are functioning perfectly; for example, in the Word file if I type:
I correctly get:
If I type:
I correctly get:
But if I type:
I get:
I just spent quite a bit of time testing the formulas provided in the different links you sent me, but nothing works...
This is software where you load a model in Word format, which allows you to automatically complete administrative documents based on data entered into this software. You can then initiate printing, and the merge does not occur on the workstation, but within the software. The output data is therefore a directly printable PDF file. What I mean is that it is not possible to make any adjustments in Word, as the merge is likely performed on a remote computer through the software. The only modifications I can make are within the Word file, and only that.
All fields are functioning perfectly; for example, in the Word file if I type:
{ MERGEFIELD CER2_DATEH \@ "yyyy" \# 0000 }, I correctly get:
2021
If I type:
{ = 1 + 1 }, I correctly get:
2
But if I type:
{ = 1 + { MERGEFIELD CER2_DATEH \@ "yyyy" \# 0000 } }, I get:
= 1 + 2021, whereas I would like to get:
2022
I just spent quite a bit of time testing the formulas provided in the different links you sent me, but nothing works...
You've said it already.
Either the calculation doesn't work, or it's in the wrong format due to the merge.
If you do: { = 365 +
what does it return?
If you have 2022, it means your merge was done in day format, if you get a fanciful result, it means the calculation works with the wrong format, if you get nothing, it means the merge from your unmodifiable data format does not provide a date or numerical format but something else.
In this regard, the syntax # 0000 addresses a decimal numerical format, not a date format, and there is therefore a contradiction in writing both "yyyy" and # 0000: we can for the test forget this switch (leave the date as is) and restart { = 1 + and { = 365 + to see what it says.
If we cannot determine the exact format of the merge (if it is neither numerical nor date, we have a problem) or if it is one or the other but no calculation is possible on it, I return to what I said, and where we need to consult someone other than me as I don't understand anything: the most reliable solution is to import your raw merge field from the formwork, and then apply a VBA macro (adapted to the imported format...)
Either the calculation doesn't work, or it's in the wrong format due to the merge.
If you do: { = 365 +
what does it return?
If you have 2022, it means your merge was done in day format, if you get a fanciful result, it means the calculation works with the wrong format, if you get nothing, it means the merge from your unmodifiable data format does not provide a date or numerical format but something else.
In this regard, the syntax # 0000 addresses a decimal numerical format, not a date format, and there is therefore a contradiction in writing both "yyyy" and # 0000: we can for the test forget this switch (leave the date as is) and restart { = 1 + and { = 365 + to see what it says.
If we cannot determine the exact format of the merge (if it is neither numerical nor date, we have a problem) or if it is one or the other but no calculation is possible on it, I return to what I said, and where we need to consult someone other than me as I don't understand anything: the most reliable solution is to import your raw merge field from the formwork, and then apply a VBA macro (adapted to the imported format...)
Hello and thank you again for your help and follow-up.
I tested:
Result:
Result:
I tested:
{ = 1 + {MERGEFIELD "CER2_DateH" \@ "yyyy" } } Result:
= 1 + 2021
{ = 365 + {MERGEFIELD "CER2_DateH" \@ "yyyy" } } Result:
= 365 + 2021
Hello,
I assume of course that the fields were entered correctly; we can perform the same test with a value that is not a date but numerical, but I fear we would arrive at the same result.
In short, the imported field is not interpreted as a numerical or date value (it’s the same thing, it’s just a serial number).
I don’t know what management software is being used; here we see that the export from an external sheet (Sage, I don't know if it's specific to the software or a syntax specific to PDF export) uses a particular syntax (exclamation point).
We can always try:
https://fairsailcustomersforlife.force.com/sagepeoplecustomercommunity/s/article/HCM-Forms-Using-Formulas-And-Merge-Fields
Edstein (already mentioned) considers this impossible, which would actually mean (since the field "2021" is correctly returned) that only specific fields dependent on the original software could be exported, and that it would not be possible to apply "mathematical operations" to them.
If the output is in text format, it may be possible to write a macro to modify this text, but it would be a workaround, since you could for example replace 202(1) with 202(2), but not calculate.
Finally, we could export PDF to DOC, but to my knowledge, this cannot be done offline with free software, leading us to turn to expensive paid software like Adobe Acrobat, and without being sure of not losing the fields from the original file in the process.
I assume of course that the fields were entered correctly; we can perform the same test with a value that is not a date but numerical, but I fear we would arrive at the same result.
In short, the imported field is not interpreted as a numerical or date value (it’s the same thing, it’s just a serial number).
I don’t know what management software is being used; here we see that the export from an external sheet (Sage, I don't know if it's specific to the software or a syntax specific to PDF export) uses a particular syntax (exclamation point).
We can always try:
https://fairsailcustomersforlife.force.com/sagepeoplecustomercommunity/s/article/HCM-Forms-Using-Formulas-And-Merge-Fields
Edstein (already mentioned) considers this impossible, which would actually mean (since the field "2021" is correctly returned) that only specific fields dependent on the original software could be exported, and that it would not be possible to apply "mathematical operations" to them.
If the output is in text format, it may be possible to write a macro to modify this text, but it would be a workaround, since you could for example replace 202(1) with 202(2), but not calculate.
Finally, we could export PDF to DOC, but to my knowledge, this cannot be done offline with free software, leading us to turn to expensive paid software like Adobe Acrobat, and without being sure of not losing the fields from the original file in the process.
I had already consulted the proposed document, but it does not provide a solution to the problem I am encountering.
In fact, the formula
{ = { MERGEFIELD CER2_DATEH \@ "yyyy" } + 1 }does not return an error, but gives the result:Jul
I'm not sure I understand the basic issue: are you saying that the database is exported to Word or to a PDF file?
In the first scenario, we can modify the fields on a copy and that's the simplest option. In the second, isn't exporting to an Excel or Access file possible? That way, we wouldn't have a problem anymore.
Operations in a merge field can be very complicated for a number of reasons: default American format, text format, data protocol..., see for example here:
https://faqword.com/index.php/word/19-publipostage/412-publipostage-decimales-dates-format-americain
Even if the format were a date, we risk having +1 added... one day, which won't change your troubles.
Regarding the fields, if there is a way to do a modifiable export, the solution is documented:
http://download.faqword.com/index.php/word-exemples/divers/file/81-champ-calcul-date-pour-publipostage
https://www.faqword.com/index.php/word/140-outils-d-edition/681-comment-ajouter-ou-supprimer-un-an-a-la-date-du-jour.
The DateCalc.zip file (my previous link) that I downloaded has, at least for me, a curious behavior, perhaps due to the fact that it's made with a very old version of Word: even if you activate compatibility mode and the macros, they remain unreadable.
In any case, it's a risky and complicated method. If it doesn't work, there's nothing left but to turn to VBA (and I don't understand anything about it).
Marina (who is an MVP Office herself and therefore presumably knows what she's talking about) has only drawn inspiration from the guru in this field (Paul Edstein, aka Macropod, who is also one and his accomplice Graham Mayor), the downside being that her historical site is inaccessible, so we need to try to see what others have reported about it.