Date range calculation problem

[Robert Collins]

I have a date field called CNTDATE and I require a field that displays the following:

if the day of CNTDATE falls between 16th and the last day of the month ,it shows the day as the 10th and the following month ie (CNT DATE = 18/1/2008)

Result = 10/2/2008

but if the CNTDATE day is between 1st and 15th (January, for example)

Result = 25/1/20008

here is what I have so far for my "calc" field:

If (Day ( CTNDATE ) ≥ 16 ; Day ( 25 ) & Month(CTNDATE +1 ) & Year ( CTNDATE ) ; Day ( 10 ) and Month ( CTNDATE ) and Year ( CTNDATE ))

Calc result is DATE

can anyone show me where I am going wrong and should I have the field as a date field with a calculated value or a Calc field?

thanks

[comment]

First you are using "&" in the first expression, "and" in the second one. Neither is correct, since the former returns a text result and the latter a logical one. You need to use the Date() function to construct a valid date from its elements. Also, Day (25) does not do what you think it does.

If (

Day ( CTNDATE ) ≥ 16 ;

Date ( Month ( CTNDATE ) + 1 ; 10 ; Year ( CTNDATE ) ) ;

Date ( Month ( CTNDATE ) ; 25 ; Year ( CTNDATE ) )

)

should I have the field as a date field with a calculated value or a Calc field?

If you don't intend to override the calculated result by a manual entry, make it a calculation field.

Edited February 19, 2008 by Guest

[Robert Collins]

thanks, I see where I went wrong.

I think your example should have read:

If ( Day ( CTNDATE ) ≥ 16 ; Date ( Month ( CTNDATE + 1 ) ; [color:red]10 ; Year ( CTNDATE ) ) ;Date ( Month ( CTNDATE ) ; [color:orange]25 ; Year ( CTNDATE ) ) )

I have just tried this and it doesn't seem to work properly - the month is not moving on

[comment]

Sorry, I had left another mistake in there - too much copying and pasting. Fixed now.

[Robert Collins]

Sorry, I had left another mistake in there - too much copying and pasting. Fixed now.

What was the other mistake?

[comment]

Closing parenthesis placed incorrectly. You had:

Month(CTNDATE +1 [color:red])

which I copied and didn't correct. It should be:

Month(CTNDATE[color:red]) +1

[Robert Collins]

Thanks so much , What a big difference a couple of () can make