Weeknumber in Word 2010 for a date function (also for a merged date field from Excel)

[XtL_D]

Complementing thread68-957685

Hello,
Word Version: Word Office Standard 2010
I had to review this formula for it didn't work at first when trying to apply to the date, let alone for a merged field date from an excel file, due to several issues - mainly regional settings - european date format / thousands and decimal format.. also I don't know if the Word version influences what works and what not, since the IF function wouldn't work neither the direct comparison inside the formula like the example given in the earlier thread (it works in Excel though). below the resulting formulas that work for me right now

so for the date alone as direct function:

{ =INT(({ DATE \@ D }+INT(({ DATE \@ M }-986/1000)*30575/1000)–{ COMPARE { DATE \@ M } > 2 }*(2-{ COMPARE { =MOD({ DATE \@ yy },4) } = 0 }-{ COMPARE { =MOD({ DATE \@ yyyy },400) } = 0 }+{ COMPARE { =MOD({ DATE \@ yy },100) } = 0 }))/7)+1 \# "KW00" }

for a date field FLDNM :

{ =(({ MERGEFIELD FLDNM \@ { IF { MERGEFIELD FLDNM \@ D } < 13 M D }}+INT(({ MERGEFIELD FLDNM \@ { IF { MERGEFIELD FLDNM \@ D } < 13 D M }}-986/1000)*30575/1000)-{ COMPARE { MERGEFIELD FLDNM \@ { IF { MERGEFIELD FLDNM \@ D } < 13 D M } } > 2 }*(2-{ COMPARE { =MOD({ MERGEFIELD FLDNM \@ yy },4)} = 0 }-{ COMPARE {=MOD({ MERGEFIELD FLDNM \@ yyyy },400)} = 0 }+{ COMPARE {=MOD({ MERGEFIELD FLDNM \@ yy },100)} = 0 }))/7)+1 \# "KW00" }

----------------------------------------- after some more corrections that you can see below in the thread, should be immune to regional settings:

Date:
{ =INT(({ DATE \@ d }+INT(({ DATE \@ M }-986/1000)*30575/1000)-({ DATE \@ M }>2)*(2-{ IF { =MOD(10,10) } = “!*” } { =(MOD({ DATE \@ yy };4)=0)+(MOD({ DATE \@ yyyy };400)=0)-(MOD({ DATE \@ yy };100)=0) } { =(MOD({ DATE \@ yy },4)=0)+(MOD({ DATE \@ yyyy },400)=0)-(MOD({ DATE \@ yy },100)=0) } }))/7)+1 \# "KW00" }


merge date field:
{ =(({ MERGEFIELD FLDNM \@ { IF { MERGEFIELD FLDNM \@ D } < 13 M D } }+INT(({ MERGEFIELD FLDNM \@ { IF { MERGEFIELD FLDNM \@ D } < 13 D M } }-986/1000)*30575/1000)-({ MERGEFIELD FLDNM \@ { IF { MERGEFIELD FLDNM \@ D } < 13 D M } }>2)*(2-{ IF { =MOD(10,10) } = “!*” } { =(MOD({ MERGEFIELD FLDNM \@ yy };4)=0)+(MOD({ MERGEFIELD FLDNM \@ yyyy };400)=0)-(MOD({ MERGEFIELD FLDNM \@ yy };100)=0) } { =(MOD({ MERGEFIELD FLDNM \@ yy },4)=0)+(MOD({ MERGEFIELD FLDNM \@ yyyy },400)=0)-(MOD({ MERGEFIELD FLDNM \@ yy },100)=0) } }))/7)+1 \# "KW00" }

 

[macropod]

The fundamental problem with your first field is that it uses an en-dash (–) instead of a minus sign/hyphen (-) for the 2nd subtraction. In any event, that field code could be reduced to:

{=INT(({DATE \@ D}+INT(({DATE \@ M}-986/1000)*30.575)-{={DATE \@ M}> 2}*(2-{=MOD({DATE \@ yy},4)=0}-{=MOD({DATE \@ yyyy},400)=0}+{=MOD({DATE \@ yy},100)=0}))/7)+1 \# "KW00"}

As for your 2nd field, all that circumlocution would be unnecessary if the dates have been input in a format consistent with the regional settings - which in any event it won't reliably address issues with days less than 13. The only thing you would still need is a test for the region, so as to know whether to use a version of the field code with a ',' separator or a ';' separator - an issue your field code still doesn't address. For example (using the first field:

{IF{=MOD(10;10)}= "!*" {=INT(({DATE \@ D}+INT(({DATE \@ M}-986/1000)*30.575)-{={DATE \@ M}> 2}*(2-{=MOD({DATE \@ yy},4)=0}-{=MOD({DATE \@ yyyy},400)=0}+{=MOD({DATE \@ yy},100)=0}))/7)+1 \# "KW00"} {=INT(({DATE \@ D}+INT(({DATE \@ M}-986/1000)*30.575)-{={DATE \@ M}> 2}*(2-{=MOD({DATE \@ yy};4)=0}-{=MOD({DATE \@ yyyy};400)=0}+{=MOD({DATE \@ yy},100)=0}))/7)+1 \# "KW00"}}



Cheers
Paul Edstein
[Fmr MS MVP - Word]

 

[XtL_D]

thank you very much for the information Paul,

"en-dash (–) instead of a minus sign - " - this happened as I worked in Word on the formula, it replaces the sign in a text..

just testing your proposal, [highlight #FCAF3E]{=MOD({DATE \@ yy}[highlight #FCE94F],[/highlight]4)=0}[/highlight] results in [highlight #FCAF3E]"!Syntaxfehler, )"[/highlight] but [highlight #8AE234]{=MOD({DATE \@ yy}[highlight #FCE94F];[/highlight]4)=0}[/highlight] works. if someone else needs to use this, he should check which one works and replace accordingly in the formulas

the regional settings here are like we are used to manage dates around here, that is DD/MM/YYYY. why the international system choose MM/DD/YYYY I guess is not in my league. but I can't and I don't want to change it in my computer because that would be a total mess for myself and my people who receive my data, mainly europeans too. in fact sometimes the issue also happens between my personal cumputer from Spain where I mainly set up all I can to work in English, and my professional computer from Germany, both with different versions of everything, Windows and Office. many formulas that I try to import from prior developments won't work. the German system requires dots (.) to recognize a date (DD.MM.YYYY), in Excel formulas "TT.MM.JJJJ". my Spanish computer doesn't regonize this, requires slash (/ --> DD/MM/YYYY)...

I found this trick of swapping month and day on some expert forum too, not that big a deal and it works perfectly - [highlight #FCE94F]when the system checks that the number for the month is higher than 12, it swaps the numbers automatically, hence the segregation only when needed.[/highlight]

so finally I keep the following versions:

{ =INT(({DATE \@ d}+INT(({DATE \@ M}-986/1000)*30575/1000)-({DATE \@ M}>2)*(2-(MOD({DATE \@ yy};4)=0)-(MOD({DATE \@ yyyy};400)=0)+(MOD({DATE \@ yy};100)=0)))/7)+1 } }

and

{ =(({ MERGEFIELD FLDNM \@ { IF { MERGEFIELD FLDNM \@ D } < 13 M D }}+INT(({ MERGEFIELD FLDNM \@ { IF { MERGEFIELD FLDNM \@ D } < 13 D M }}-986/1000)*30575/1000)-({ MERGEFIELD FLDNM \@ { IF { MERGEFIELD FLDNM \@ D } < 13 D M } }>2)*(2-(MOD({ MERGEFIELD FLDNM \@ yy };4)=0)-(MOD({ MERGEFIELD FLDNM \@ yyyy };400)=0)+(MOD({ MERGEFIELD FLDNM \@ yy };100)=0)))/7)+1 \# "KW00" }

thanks a lot!

 

[macropod]

just testing your proposal, {=MOD({DATE \@ yy},4)=0} results in "!Syntaxfehler, )" but {=MOD({DATE \@ yy};4)=0} works. if someone else needs to use this, he should check which one works and replace accordingly in the formulas

That is because your system uses non-English regional settings, which is why I provided the second field code to handle both English and non-English settings. There is no need for anyone to change their regional settings or edit the field code if you take that approach.

Cheers
Paul Edstein
[Fmr MS MVP - Word]

 

[XtL_D]

ohhh got you now, I am maybe so slow right now cause I am so tired sorry,

I can then still simplify a bit further if I test this condition inside the formula where I need it for the MOD calculations

KW number for the current date (should be directly applicable to the different date document properties if needed, for I expect they will all be using the english international settings):
{ =INT(({ DATE \@ D }+INT(({ DATE \@ M }-986/1000)*30575/1000)-({ DATE \@ M }>2)*(2-{ IF { =MOD(10,10) } = “!*” } { =(MOD({ DATE \@ yy };4)=0)+(MOD({ DATE \@ yyyy };400)=0)-(MOD({ DATE \@ yy };100)=0) } { =(MOD({ DATE \@ yy },4)=0)+(MOD({ DATE \@ yyyy },400)=0)-(MOD({ DATE \@ yy },100)=0) } }))/7)+1 \# "KW00" }

KW number for merge date field:
{ =(({ MERGEFIELD FLDNM \@ { IF { MERGEFIELD FLDNM \@ D } < 13 M D } }+INT(({ MERGEFIELD FLDNM \@ { IF { MERGEFIELD FLDNM \@ D } < 13 D M } }-986/1000)*30575/1000)-({ MERGEFIELD FLDNM \@ { IF { MERGEFIELD FLDNM \@ D } < 13 D M } }>2)*(2-{ IF { =MOD(10,10) } = “!*” } { =(MOD({ MERGEFIELD FLDNM \@ yy };4)=0)+(MOD({ MERGEFIELD FLDNM \@ yyyy };400)=0)-(MOD({ MERGEFIELD FLDNM \@ yy };100)=0) } { =(MOD({ MERGEFIELD FLDNM \@ yy },4)=0)+(MOD({ MERGEFIELD FLDNM \@ yyyy },400)=0)-(MOD({ MERGEFIELD FLDNM \@ yy },100)=0) } }))/7)+1 \# "KW00" }

thanks again!

-------------------------------------------------- complementary information since I can no longer correct the first posting

the [highlight #FCE94F]formula above[/highlight] for a merged date field includes a swap for M and D [highlight #FCE94F]for systems using non-English regional settings[/highlight] (would there then still be differences between the various English regions?? you might want to check that if the results are unexpected). I can't think of a trick to make it better right now. - but maybe someone will , in that case please feel free to share with us!

to show a merge date field in the "DD.MM.YY" format - for non English based system:
{ MERGEFIELD FLDNM \@ "{ IF { MERGEFIELD FLDNM \@ "D" } < 13 "MM.DD" "DD.MM" }.YYYY" }

so for systems using the [highlight #FCE94F]English international settings, the formula below[/highlight] doesn't need the swap:

KW number for a merge date field:
{ =(({ MERGEFIELD FLDNM \@ D }+INT(({ MERGEFIELD FLDNM \@ M }-986/1000)*30575/1000)-({ MERGEFIELD FLDNM \@ M }>2)*(2-{ IF { =MOD(10,10) } = “!*” } { =(MOD({ MERGEFIELD FLDNM \@ yy };4)=0)+(MOD({ MERGEFIELD FLDNM \@ yyyy };400)=0)-(MOD({ MERGEFIELD FLDNM \@ yy };100)=0) } { =(MOD({ MERGEFIELD FLDNM \@ yy },4)=0)+(MOD({ MERGEFIELD FLDNM \@ yyyy },400)=0)-(MOD({ MERGEFIELD FLDNM \@ yy },100)=0) } }))/7)+1 \# "KW00" }

 

[Andrzejek]

Wouldn't that be the case for writing a User Defined Function instead?
It involves VBA, but it is a lot easier to write, modify, see what's going on, test, and you can include comments.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[XtL_D]

thanks Andy,

I do programm in VBA too, and I did some testing to fill purely through macros my different forms and documents that I need, it took way tooooooo lonnnnnng to execute.

in the end merging is the fastest solution in the state of how the computer and the data are stored and configurated - and my level of skill maybe for programming VBA - and it is also very flexible and easy to keep my templates up to date the whole time

only sometimes some stuff won't work and I have to get it solved, like this particular failure with the date fields in merged documents - not bad in the end cause I just get to learn some more new stuff!!

regarding user defined functions, I know you can call them from excel cells if it still works, long time that I haven't used them that way, I am not sure it works within a word field, your link doesn't clarify this

anyways, I have tried to programm and install some AddIns as I have already done in the past, somehow it doesn't work - we connect to all the data on the servers through a "farm", the applications are also stored / available on this farm, I couldn't find the way to install any addin on word, excel, outlook neither.. this is a new environment to me, I never had something like this in my prior jobs.

I am no professional programmer, and I don't think my IT people know much about this, from how all this stuff work around here.. so that will have to do like it is

thanks anyways! bye

 

[macropod]

Wouldn't that be the case for writing a User Defined Function instead?

Not really, since the ultimate goal seems to be to generate the output in a mailmerge. A UDF would require much more effort to deploy in that context.

Cheers
Paul Edstein
[Fmr MS MVP - Word]

 

[macropod]

merge date field:
{ =(({ MERGEFIELD FLDNM \@ { IF { MERGEFIELD FLDNM \@ D } < 13 M D }}+INT(({ MERGEFIELD FLDNM \@ { IF { MERGEFIELD FLDNM \@ D } < 13 D M }}-986/1000)*30575/1000)-({ MERGEFIELD FLDNM \@ { IF { MERGEFIELD FLDNM \@ D } < 13 D M } }>2)*(2-{ IF { =MOD(10,10) } = “!*” } { =(MOD({ MERGEFIELD FLDNM \@ yy };4)=0)+(MOD({ MERGEFIELD FLDNM \@ yyyy };400)=0)-(MOD({ MERGEFIELD FLDNM \@ yy };100)=0) } { =(MOD({ MERGEFIELD FLDNM \@ yy },4)=0)+(MOD({ MERGEFIELD FLDNM \@ yyyy },400)=0)-(MOD({ MERGEFIELD FLDNM \@ yy },100)=0) } }))/7)+1 \# "KW00" }

You really don't seem to have paid attention to what I wrote about not needing to test the day/month. All you should need is:
{SET MDATE {MERGEFIELD FLDNM}}{=INT(({MDATE \@ D}+INT(({MDATE \@ M}-986/1000)*30.575)-{={MDATE \@ M}> 2}*(2-{IF{=MOD(10;10)}= "!*" {=MOD({MDATE \@ yy},4)=0}-{=MOD({MDATE \@ yyyy},400)=0}+{=MOD({MDATE \@ yy},100)=0} {=MOD({MDATE \@ yy};4)=0}-{=MOD({MDATE \@ yyyy};400)=0}+{=MOD({MDATE \@ yy},100)=0}}))/7)+1 \# "KW00"}


Cheers
Paul Edstein
[Fmr MS MVP - Word]

 

[XtL_D]

Hello Paul,

You really don't seem to have paid attention to what I wrote..

right with this remark I think I can say the same about you

removing the D/M swap won't work here due to all the reasons I have explained. in fact the results were all wrong until I remembered to include the swap in this formula too.

I am not sure there will be any easy trick to check the need for it - like the real smart failure test to determine ",/;" for the second part of the formula -, and I have to live with it

as I already stated, setting the system to store the dates in the excel file(s) with the international "MM/DD/YYYY" is not an option, and it wouldn't even be practical either in my context and environment.

and I think there might be other people out there with the same situation who can use this information

you don't agree but that is your choice, it just doesn't fit my reality right now. beeing pragmatical here.

so let's leave it there thank you and enjoy your weekend!

 

[macropod]

If if someone has entered dates in the wrong format for the workbook, especially if they've dome it for some dates and not others, there is no reliable way of correcting it via field coding or even vba. In other words, manual intervention will likely be required. Hence, your D/M test risks giving only the illusion of a corrected result - it actually risks falsifying correct results!

If you're working with dates that have to be input in multiple regions, I'd highly recommend ensuring the relevant cells are formatted for dates using the ISO date format (i.e. YYYY MM DD) Alternatively, ensure the relevant cells are formatted for dates with months formatted as MMMM or MMMM.

Cheers
Paul Edstein
[Fmr MS MVP - Word]

 

[XtL_D]

it is all good as it is.

so you might understand why it is already solved with my last version:

it is MY databank in MY excel file that I fill with registers about the topics I need to work on. among the data there are several date trackers for each register.

since I gather the data from different sources, emails, databanks, ERP systems, scanned documents, OCR when possible.., I MYSELF tip most of the data that I can't paste directly. noone else enters any data directly in MY table. so the dates in it all have the same format, which is the format I know how to work with. sometimes I have to copy some data from a foreign file eventually with other settings, then I know I have to check the data afterwards. it is never so much at a time so it is timely doable and I can survive it.

then I use the merging function in Word with several merge documents priorily prepared (kind of my document templates), in order to issue different documents in Word or PDF format that I need to dispatch to different people (sometimes in different countries), so they can be informed and do what they are required to do about the topic at hand.

and in case someone intern would need to consult my excel file in my abscence, they would also understand the data correctly.
and in case someone intern to the company copies part of my data to his/her own file, since we have all the same background settings, there should be no compatibility issue.
in case someone from oveseas copies some of my data to his / her own file, well I don't actually share my excel file with them, so I don't think this can happen.

in conclusion, as it is, the swap works for me and may also work for someone with a similar work environment.

this doesn't need to go further for me, now is officially weekend here so let's start with this!

 

[SkipVought]

@XtL_D,

setting the system to store the dates in the excel file(s) with the international "MM/DD/YYYY" is not an option

Just wanted to clarify an issue about entering dates in Excel that not many people realize, but I'm almost certain that you know.

Dates are actually stored as numbers, the date-numbering sequence in Excel, starting a 1 for 1900 Jan 1.

When the user keys numeric characters into a cell and hits ENTER, Excel evaluates the string and then may or not do some conversion before storing the result for the cell. Of course there may also be other characters =,/,-,+,'. The sequence and context is all considered.

Here's what I've found Stateside. Not exactly sure what gets displayed with other date settings:
7/10 ==> 44752 the stored date value, displayed in the cell as 10-Jul and in the Formula Bar as 7/10/2022

The bottom line is that Excel evaluates what looks like a date or part of a date and makes a conversion to a pure integer. The numeric characters you ENTERED no longer exist in that cell. You can read a little more at faq68-7375.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[XtL_D]

Thank you Skip,

yes you are right to assume that I know there is a difference between the stored and displayed values.

with my default settings, this number 44752 when displayed as a date becomes 10.07.2022 - 10th of July under the European (German) format. when this data is treated within Excel, for example a pivot table, this field will be correctly interpreted and my be regrouped as July.

when merging in a Word document however, without any formatting, it switches to 7/10/2022, due to it automatically uses the English US format. this is what I can't have, for not a soul here will get it right.

formatting after the European format again corrects the date.

then comes the problems, when I need to format the dates otherwise, either "YYYYMMDD" (or "yyyyMMdd" same results) or calculate the week number, below an example with different dates and how they get formatted and calculated, with and without the swap. as you can see, only with the swap do I get the correct data.

so I don't know what you all expect for me to do to get rid of the swap, it can only generate compatibility issues with my colleagues when sharing file data if I kind of change whatever setting that I am not even sure how, if we copy / paste values my experience with chinese data was that it then came all wrong for example..

the swap solves my problem, and anything else I simply find too risky for I haven't seen that it is easy to set up neither that it works yet.

and I trust there might be also other people out there with a level of skills similar to mine, with eventually some similar data / merging / date formatting issue, who can use this information here. I would have liked to find it, much easier. unfortunately I only found bits of it that I had to put together and this is the result

thanks again, have a good start in the new week!

 

[macropod]

with my default settings, this number 44752 when displayed as a date becomes 10.07.2022 - 10th of July under the European (German) format. when this data is treated within Excel, for example a pivot table, this field will be correctly interpreted and my be regrouped as July.

when merging in a Word document however, without any formatting, it switches to 7/10/2022, due to it automatically uses the English US format. this is what I can't have, for not a soul here will get it right.

The default US date output format is irrelevant. If you need a different format, you really should learn to use mailmerge field switches. See my Mailmerge Tips & Tricks page:

https://www.msofficeforums.com/mail-merge/21803-mailmerge-tips-tricks.html

Cheers
Paul Edstein
[Fmr MS MVP - Word]