Last Month DAY() in Formula

[Former Member]

I am trying to write a formula that uses a date parameter selected by the user to calculate a Last Month's MTD total. I am having trouble setting up the formula to handle when the dates are not in the same day range. For example, the parameter is 07/31/2016, but there is only 30 days in June (last month).

Parameter Date: 07/31/2016 ({?EndDate})

If DATE({date field}) >= DATE(YEAR({?EndDate}), (MONTH({?EndDate})-1,1) and

DATE ({date field}) <= DATE(YEAR({?EndDate}), MONTH({?EndDate}),1)-1) then

Result I want:

Data from range 06/01/2016 to 06/30/2016

The above formula will not work when the Parameter date is today's date. How can I change the formula to make it work for all instances? Thank you.

[Former Member · Accepted solution]

We can get previous month first/last day as mentioned below

[Former Member · Accepted solution]

The whole month is not really the issue. For instance, if I choose the parameter date of today, I only want to see last month's MTD data. i.e 07/01 to 07/11.

[Former Member · Accepted solution]

For two parameter dates scenario, i.e. parameter date as 20160811 and another with parameter date as 20160731, same code will work as shown below.

[abhilash_kumar · Accepted solution]

This should hopefully work:

If Date({date fied}) IN Date(DateAdd('m', - 1, {?EndDate}- Day({?EndDate})) + 1) TO

date(DateAdd('m',-1,{?End Date})) then

If it throws a 'data time required here' error and highlights the last line of the code, just remove the 'date' function:

If Date({date fied}) IN Date(DateAdd('m', - 1, {?EndDate}- Day({?EndDate})) + 1) TO

(DateAdd('m',-1,{?End Date})) then

-Abhilash

[Former Member · Accepted solution]

Abhilash, this is working except when my parameter date is 07/31/2016, it is pulling last month's data between 05/31/2016 and 06/30/2016. I think I fixed this by changing the 1st part of the line to:

If Date({date fied}) IN Date(YEAR({?End Date}), MONTH({?End Date})-1,1) TO (DateAdd('m',-1,{?End Date})) then...

Thank you EVERYONE for all your help.

[abhilash_kumar · Accepted solution]

You will have an issue when the user selects a date in January with that code.

I actually looked at my formula again, and I see that I messed up a bracket.

Here's the correct one that should work fine:

If Date({date fied}) IN Date(DateAdd('m',-1,{?End Date}- Day({?End Date}) + 1)) TO

Date(DateAdd('m',-1,{?End Date})) then

-Abhilash

[Former Member · Accepted solution]

Thank you again for your help.

[DellSC]

Your formula also doesn't account for different years if, for example, the value of {?EndDate} is 1/31/2016.  So, I would try something like this:

Datevar endDt := {?EndDate}) - Day({?EndDate});  <--Gives the end day of the previous month

DateVar startDt :=  Date(Year(endDt), Month(endDt), 1); <--Gives the first day of the previous month

If Date({Date Field}) >= startDt and Date({Date Field}) <= endDt then...

-Dell

[Former Member]

This produced all the data in the month, not just MTD. For instance, my parameter is today's date. It gives me all of the data between 07/01 and 07/31.

[DellSC]

I didn't realize you were looking for MTD.  Try modifying the first line like this:

Datevar endDt := DateAdd('m', -1, {?EndDate});

-Dell

[Former Member]

It highlights the formula and says "a date is required here".

[DellSC]

Try this:

Datevar endDt := DateAdd('m', -1, Date({?EndDate}));

-Dell

[Former Member]

It still does not want to work. The message changed to "a date-time is required here".

[DellSC]

Where in the formula is it highlighting when you get the message?  What is the Data Type of your {?End Date} parameter?

-Dell

[Former Member]

It is highlighting only the parameter {?EndDate}. The parameter type is "Date". It is not linked or used in Record Selection. It is only for the purposes of the formula.

[DellSC]

Which version of Crystal are you using?  (Go to Help>>About and get the full version number.)  Can you save the report with data without using the formula we're discussing (leave the "bad" version of the formula in the formula list...) and attach it here?  To attach it, you'll have to go to the "Advanced" editor and you'll have to add ".txt" on the end of the file name (it won't allow you to upload a file with a .rpt extension)

-Dell

[Former Member]

We are using CR 2013 SP 4 Version 14.1.4.1327.

Can I send this only to you? or is there a way to change some of the data? It is sensitive data. I can send you a screenshot of the working report if that helps.

[DellSC]

Yes.  Please get my email address from my website:  http://www.dellstinnett.com

-Dell

[abhilash_kumar]

Hi,

Try:

If Date({date fied}) IN Date(DateAdd('m', - 1, {?EndDate}- Day({?EndDate})) + 1) TO

Date({?EndDate}- Day({?EndDate}))) then

-Abhilash

[Former Member]

I tried this, but I get the error "keyword 'then' is missing" even though it is there after the ")". Not sure why I am getting that message. If I reduce the ")", then I get the message that "A date-time is required here" highlighting after the "TO".