Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Unexpected result in date calculations
I created a date calculation to determine if an input date is past or not by
comparing the sums of the year x 10000, the month x 100 and the date of the month with the sums for todays date. This calculation has had surprising results. The current date, month and year were set in a separate equation field specifically with: {SET yy {Date \@ yyyy}} {SET mm {Date \@ M}} {Set dd {Date \@ d}} In another part of the form, the dates are compared to identify which text to print: i.e., if today's calc mydate calc "write this" "or this" The left side of the less than operator equation is {yy*100000}+{mm*100)+dd The right side of the less than operator is the quation is: ({mydatge \@ yyyy}*10000) + ({mydate \@ MM}*100) + {Mydate \@ d} The details of the calculation are unexpected. For todays date of for example 1/1/09, the answer should be and is: 200900000+100+1 or 200900101 when I do the calculation for the input date 1/31/09, the answer should be 200900000 + 100 + 31 or 200900131 making the comparison False. This does not happen. The comparison result was True. When I played with the calculation, the answer for mydate was 2009000069. When I broke up the parts of the date, the numbers were correct. But the calculation changed the plus before the day value to a minus, effectively 100-31 yielding 69. Can anyone make sense why it does this? Thanks! |
#2
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Unexpected result in date calculations
Did you actually use this:
{yy*100000}+{mm*100)+dd If so, you should try {=yy*100000+mm*100+dd} or perhaps even { =({yy}*100000)+({mm}*100)+{dd} } where all the {} are the special field code braces you can insert using ctrl-F9 (Not convinced this is the problem you are experiencing but it seems a good place to start) Peter Jamieson http://tips.pjmsn.me.uk gouved wrote: I created a date calculation to determine if an input date is past or not by comparing the sums of the year x 10000, the month x 100 and the date of the month with the sums for todays date. This calculation has had surprising results. The current date, month and year were set in a separate equation field specifically with: {SET yy {Date \@ yyyy}} {SET mm {Date \@ M}} {Set dd {Date \@ d}} In another part of the form, the dates are compared to identify which text to print: i.e., if today's calc mydate calc "write this" "or this" The left side of the less than operator equation is {yy*100000}+{mm*100)+dd The right side of the less than operator is the quation is: ({mydatge \@ yyyy}*10000) + ({mydate \@ MM}*100) + {Mydate \@ d} The details of the calculation are unexpected. For todays date of for example 1/1/09, the answer should be and is: 200900000+100+1 or 200900101 when I do the calculation for the input date 1/31/09, the answer should be 200900000 + 100 + 31 or 200900131 making the comparison False. This does not happen. The comparison result was True. When I played with the calculation, the answer for mydate was 2009000069. When I broke up the parts of the date, the numbers were correct. But the calculation changed the plus before the day value to a minus, effectively 100-31 yielding 69. Can anyone make sense why it does this? Thanks! |
#3
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Unexpected result in date calculations
Hi Goved,
There's no need for the complicated maths. Simply use a formula field coded along the lines of: {IF{DATE \@ YYYYMMDD} {MyDate \@ YYYYMMDD} "This" "That"} -- Cheers macropod [MVP - Microsoft Word] "gouved" wrote in message ... I created a date calculation to determine if an input date is past or not by comparing the sums of the year x 10000, the month x 100 and the date of the month with the sums for todays date. This calculation has had surprising results. The current date, month and year were set in a separate equation field specifically with: {SET yy {Date \@ yyyy}} {SET mm {Date \@ M}} {Set dd {Date \@ d}} In another part of the form, the dates are compared to identify which text to print: i.e., if today's calc mydate calc "write this" "or this" The left side of the less than operator equation is {yy*100000}+{mm*100)+dd The right side of the less than operator is the quation is: ({mydatge \@ yyyy}*10000) + ({mydate \@ MM}*100) + {Mydate \@ d} The details of the calculation are unexpected. For todays date of for example 1/1/09, the answer should be and is: 200900000+100+1 or 200900101 when I do the calculation for the input date 1/31/09, the answer should be 200900000 + 100 + 31 or 200900131 making the comparison False. This does not happen. The comparison result was True. When I played with the calculation, the answer for mydate was 2009000069. When I broke up the parts of the date, the numbers were correct. But the calculation changed the plus before the day value to a minus, effectively 100-31 yielding 69. Can anyone make sense why it does this? Thanks! |
#4
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Unexpected result in date calculations
I will try this, it does seem simpler. Thanks.
"macropod" wrote: Hi Goved, There's no need for the complicated maths. Simply use a formula field coded along the lines of: {IF{DATE \@ YYYYMMDD} {MyDate \@ YYYYMMDD} "This" "That"} -- Cheers macropod [MVP - Microsoft Word] "gouved" wrote in message ... I created a date calculation to determine if an input date is past or not by comparing the sums of the year x 10000, the month x 100 and the date of the month with the sums for todays date. This calculation has had surprising results. The current date, month and year were set in a separate equation field specifically with: {SET yy {Date \@ yyyy}} {SET mm {Date \@ M}} {Set dd {Date \@ d}} In another part of the form, the dates are compared to identify which text to print: i.e., if today's calc mydate calc "write this" "or this" The left side of the less than operator equation is {yy*100000}+{mm*100)+dd The right side of the less than operator is the quation is: ({mydatge \@ yyyy}*10000) + ({mydate \@ MM}*100) + {Mydate \@ d} The details of the calculation are unexpected. For todays date of for example 1/1/09, the answer should be and is: 200900000+100+1 or 200900101 when I do the calculation for the input date 1/31/09, the answer should be 200900000 + 100 + 31 or 200900131 making the comparison False. This does not happen. The comparison result was True. When I played with the calculation, the answer for mydate was 2009000069. When I broke up the parts of the date, the numbers were correct. But the calculation changed the plus before the day value to a minus, effectively 100-31 yielding 69. Can anyone make sense why it does this? Thanks! |
#5
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Unexpected result in date calculations
The mismatched right bracket is a typo, and should be "}".
The equation does generate results, but my question was more why the right side of the statement: If {= (yy*10000)+(mm*100)+dd} { =({mydate \@ yy}*100000)+({mydate \@ mm}*100)+{mydate \@ d} } the last plus (for mydate \@ d) acts like a minus. "Peter Jamieson" wrote: Did you actually use this: {yy*100000}+{mm*100)+dd If so, you should try {=yy*100000+mm*100+dd} or perhaps even { =({yy}*100000)+({mm}*100)+{dd} } where all the {} are the special field code braces you can insert using ctrl-F9 (Not convinced this is the problem you are experiencing but it seems a good place to start) Peter Jamieson http://tips.pjmsn.me.uk gouved wrote: I created a date calculation to determine if an input date is past or not by comparing the sums of the year x 10000, the month x 100 and the date of the month with the sums for todays date. This calculation has had surprising results. The current date, month and year were set in a separate equation field specifically with: {SET yy {Date \@ yyyy}} {SET mm {Date \@ M}} {Set dd {Date \@ d}} In another part of the form, the dates are compared to identify which text to print: i.e., if today's calc mydate calc "write this" "or this" The left side of the less than operator equation is {yy*100000}+{mm*100)+dd The right side of the less than operator is the quation is: ({mydatge \@ yyyy}*10000) + ({mydate \@ MM}*100) + {Mydate \@ d} The details of the calculation are unexpected. For todays date of for example 1/1/09, the answer should be and is: 200900000+100+1 or 200900101 when I do the calculation for the input date 1/31/09, the answer should be 200900000 + 100 + 31 or 200900131 making the comparison False. This does not happen. The comparison result was True. When I played with the calculation, the answer for mydate was 2009000069. When I broke up the parts of the date, the numbers were correct. But the calculation changed the plus before the day value to a minus, effectively 100-31 yielding 69. Can anyone make sense why it does this? Thanks! |
#6
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Unexpected result in date calculations
Thank you so much. It needs quotation marks around "YYYYMMd" to work, and it
works like a charm. "macropod" wrote: Hi Goved, There's no need for the complicated maths. Simply use a formula field coded along the lines of: {IF{DATE \@ YYYYMMDD} {MyDate \@ YYYYMMDD} "This" "That"} -- Cheers macropod [MVP - Microsoft Word] "gouved" wrote in message ... I created a date calculation to determine if an input date is past or not by comparing the sums of the year x 10000, the month x 100 and the date of the month with the sums for todays date. This calculation has had surprising results. The current date, month and year were set in a separate equation field specifically with: {SET yy {Date \@ yyyy}} {SET mm {Date \@ M}} {Set dd {Date \@ d}} In another part of the form, the dates are compared to identify which text to print: i.e., if today's calc mydate calc "write this" "or this" The left side of the less than operator equation is {yy*100000}+{mm*100)+dd The right side of the less than operator is the quation is: ({mydatge \@ yyyy}*10000) + ({mydate \@ MM}*100) + {Mydate \@ d} The details of the calculation are unexpected. For todays date of for example 1/1/09, the answer should be and is: 200900000+100+1 or 200900101 when I do the calculation for the input date 1/31/09, the answer should be 200900000 + 100 + 31 or 200900131 making the comparison False. This does not happen. The comparison result was True. When I played with the calculation, the answer for mydate was 2009000069. When I broke up the parts of the date, the numbers were correct. But the calculation changed the plus before the day value to a minus, effectively 100-31 yielding 69. Can anyone make sense why it does this? Thanks! |
#7
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Unexpected result in date calculations
It should be yyyyMMdd and not yyyMMd
-- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org gouved wrote: Thank you so much. It needs quotation marks around "YYYYMMd" to work, and it works like a charm. "macropod" wrote: Hi Goved, There's no need for the complicated maths. Simply use a formula field coded along the lines of: {IF{DATE \@ YYYYMMDD} {MyDate \@ YYYYMMDD} "This" "That"} -- Cheers macropod [MVP - Microsoft Word] "gouved" wrote in message ... I created a date calculation to determine if an input date is past or not by comparing the sums of the year x 10000, the month x 100 and the date of the month with the sums for todays date. This calculation has had surprising results. The current date, month and year were set in a separate equation field specifically with: {SET yy {Date \@ yyyy}} {SET mm {Date \@ M}} {Set dd {Date \@ d}} In another part of the form, the dates are compared to identify which text to print: i.e., if today's calc mydate calc "write this" "or this" The left side of the less than operator equation is {yy*100000}+{mm*100)+dd The right side of the less than operator is the quation is: ({mydatge \@ yyyy}*10000) + ({mydate \@ MM}*100) + {Mydate \@ d} The details of the calculation are unexpected. For todays date of for example 1/1/09, the answer should be and is: 200900000+100+1 or 200900101 when I do the calculation for the input date 1/31/09, the answer should be 200900000 + 100 + 31 or 200900131 making the comparison False. This does not happen. The comparison result was True. When I played with the calculation, the answer for mydate was 2009000069. When I broke up the parts of the date, the numbers were correct. But the calculation changed the plus before the day value to a minus, effectively 100-31 yielding 69. Can anyone make sense why it does this? Thanks! |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date field calculations | Microsoft Word Help | |||
Merge and Sort - unexpected result | Mailmerge | |||
Date form field incorrect result | Microsoft Word Help | |||
Performing Date calculations in Word | Microsoft Word Help | |||
Date calculations | Mailmerge |