Crystal Date Formula

[neilmcdonald]

Could anyone tell me if there is a formula to calculate the next occurence of date? E.g if the date is 01.01.03, the next occurrence is 01.01.04, but if the date is 01.11.01, the next date is 01.11.04.

I'm sure ther's an easy way, but I don't think I can see the wood for the trees!

Thanks,

 

[kenhamady]

Why does your second example skip 3 days?
Are you talking about skipping weekends?

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[mbarron]

What are the correlations between the dates? This E.g if the date is 01.01.03, the next occurrence is 01.01.04, but if the date is 01.11.01, the next date is 01.11.04.
give no insight on any kind of relationship of the next date.

What is the format of the date? The reason for my qustion is because Tek-tips is worldwide and your second set of dates could be interpretted as either November 1st or January 11th, depending who is looking at it.

Mike

 

[neilmcdonald]

apologies for any confusion - the date format is dd.mm.yy

 

[mbarron]

You didn't aswer the big question What is the relationship between the dates?

Mike

 

[kenhamady]

Why does the first example jump 1 year but the the second example jumps 3?

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[neilmcdonald]

Sorry for not making this clear...

The dates refer to contract expiry dates. The contracts are renewed annually unless the customer cancels. The report that I am creating looks at the very first contract expiry date, which may be several years ago e.g. Jan 1st 2001. I then need to calculate the next time their contract is up for renewal, which will be Jan 1st 2004. In other words, I'm need to work out the next occurence of January 1st, after today's date.

My example of 1.11.01 to 1.11.04 was incorrect - should have been 1.11.01 to 1.11.03, as we're not yet into November.

Hope this helps!

 

[kenhamady]

Try this:

if
Date ( Year (CurrentDate) , Month ({expiry}) , Day ({expiry}) )
> current date
then
Date ( Year (CurrentDate) , Month ({expiry}) , Day ({expiry}) )
else
Date ( Year (CurrentDate)+1, Month ({expiry}) , Day ({expiry}) )

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[neilmcdonald]

Thanks Ken - that seems to work perfectly. Perhaps you can help me with the final piece of the puzzle...

For the contracts to be cancelled, the customer must give 90 days notice, so I need to amend the formula to reflect this. If the date coming from the calculation you gave me earlier falls within the next 90 days, I need to add another year to it.

I appreciate your help - thanks!

 

[kenhamady]

Try using CurrentDate+90 instead of Current Date

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[neilmcdonald]

Excellent! Thanks very much for your help!