Unexpected result in date calculations

[gouved]

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!

 

[Peter Jamieson]

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

 

[macropod]

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"}

 

[gouved]

I will try this, it does seem simpler. Thanks.

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]

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!

 

[gouved]

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.

 

[gouved]

Thank you so much. It needs quotation marks around "YYYYMMd" to work, and it
works like a charm.

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]

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!

 

[Graham Mayor]

It should be yyyyMMdd and not yyyMMd

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

Thank you so much. It needs quotation marks around "YYYYMMd" to
work, and it works like a charm.

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]

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!