Help with DateAdd Formula 1

[khan82]

I am trying to calculate the date range (from the 1st of the month to the last day) of a month three months from now.

I tried the formula below but the problem is that its adding months based on the date today and not the first. how can alter the currentdate formula to return the current date as the 1st of a month?

{PML_LEASE__LEASE.Original_Lease_End}> DateAdd ("m",3 ,[highlight #FCE94F]CurrentDate[/highlight] ) And {PML_LEASE__LEASE.Original_Lease_End}<DateAdd ("m",5 ,[highlight #FCE94F]CurrentDate[/highlight] )

Thanks

 

[briangriffin]

Leverage the predefined date ranges to make these types of formulas easier:

{PML_LEASE__LEASE.Original_Lease_End}> DateAdd ("m",3 ,minimum(lastfullmonth) )
And
{PML_LEASE__LEASE.Original_Lease_End}<DateAdd ("m",5 ,minimum(lastfullmonth) )

 

[briangriffin]

I may have misinterpreted, if you want three months from the first of the current month, then you could do it a couple of ways - change the numeric value in the dateadds, or change to this: minimum(monthtodate) ** Could be minimum(MTD), can't test.

I think the operator in the first part needs to be greater than or equal to, since it evaluates to 8/1/2013. The second part is good, since it would result in 10/1/2013.

 

[pmax9999]

Not sure I understand exactly what date range you are looking for. I have interpreted it to be:

from: the first of "current month" + 3 months (ie 1st August)
to: the last day of "current month" + 5 months (ie 31st October)

If this is correct, try this (I don't currently have access to Crystal, so apologies if I have messed up the syntax):

{PML_LEASE__LEASE.Original_Lease_End} >= Date(Year(CurrentDate), Month(CurrentDate)+3, 1) and
{PML_LEASE__LEASE.Original_Lease_End} <  (Date(Year(CurrentDate), Month(CurrentDate)+6, 1) - 1)

Hope this helps.

Cheers
Pete

 

[khan82]

Basically I want to run a report in any given month that has a date range of 3 months from then.

So in May 2013 I want the system to run a report with a date rane of Aug 1-31, 2013.

This way the user doesn't have to enter the date ranges and I could automate the report.

I will try the syntax above and see what happens

Thanks for your time and valuable advice!

 

[pmax9999]

If it will always be a full month in 3 months time, amend the code to:

{PML_LEASE__LEASE.Original_Lease_End} >= Date(Year(CurrentDate), Month(CurrentDate)+3, 1) and
{PML_LEASE__LEASE.Original_Lease_End} <=(Date(Year(CurrentDate), Month(CurrentDate)+4, 1) - 1)

Cheers
Pete

 

[khan82]

worked like a charm Pete. Thanks

 

[pmax9999]

Just thinking it through a little further, I would amend it slightly as follows:

{PML_LEASE__LEASE.Original_Lease_End} >= DateSerial(Year(CurrentDate), Month(CurrentDate)+3, 1) and
{PML_LEASE__LEASE.Original_Lease_End} <=(DateSerial(Year(CurrentDate), Month(CurrentDate)+4, 1) - 1)

By amending the formula to use DateSerial rather than Date, it will avoid the problem that would otherwise arise later in the year when adding 3 months and getting a number higher than 12.

Cheers
Pete