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:
for a date field FLDNM :
----------------------------------------- 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" }
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:
Code:
{ =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 :
Code:
{ =(({ 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" }