Need Crystal Date Formula Help

Hello All...

So, I know this is something likely very simple and silly, but we're stuck.  What we're trying to do is to have a Formula Field, with something like the following:

DateAdd("m", 1, DateTime( Year({CurrentDate}), Month({CurrentDate), 1, 0,0,0 )) - 1

...such that we can have Crystal display the END of the CURRENT month (meaning "2014-12-31" if run *today*).

But, the 'CurrentDate' function, doesn't seem to work in the Formula above.  BTW...we *ALSO* need, the BEGINNING of the PRIOR 3rd MONTH.  In other words, we need a SECOND Formula, which shows "2014-09-01"...assuming *today* is "2014-12-22".

Please help!  We need to finalize these reports before the XMas break.  THANK YOU!...Mark
datatechcorpAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
vastoConnect With a Mentor Commented:
Try
DateAdd("m", 1, DateTime( Year(CurrentDate), Month(CurrentDate), 1, 0,0,0 )) - 1

This should work too:
DateAdd("m", 1, DateTime( Year(CurrentDate), Month(CurrentDate), 1 )) - 1

for the prior 3rd month try

DateAdd("m", -3, DateTime( Year(CurrentDate), Month(CurrentDate), 1 ))
0
 
datatechcorpAuthor Commented:
OMG...thank you VASTO sooooo much!  My bad on that...I'd copied in an old formula, and didn't realize...that I still had the "braces" intact...from the old field I was copying this from.  I've looked at this sideways for over an hour :-)

Again, thank you!...Mark
0
 
vastoCommented:
You are welcome Mark!

BTW having the date hardcoded will work if you want always to use the report for a fixed period: last quarter, last month etc. However if you would like to use the same report for different periods you can create parameters for the beginning and the end of the period and control the default values using the software, which runs the reports. In this way you can predefine periods like last month , last quarter, last year , last week etc. and you can run the report with the predefined values or manually select any other set of values. This will make the report more customizable.

You can check this video : http://www.r-tag.com/Pages/Preview_Demo.aspx
From the menu in left Jump directly to "Parameter templates", or go to time 14:45.

This tool has a free Community edition in case it might be useful. There are plenty of other viewers too. I guess there should be some more which support parameter templates.

This approach will allow you to reduce the number of reports. You can also use predefined set of values inside the report. For example if set =1 use last month , if set =2 use last year etc. but you should handle this inside each report, while the software will handle it for all at once. Also ,internal handling will not allow you to use the report with free dates.
0
 
datatechcorpAuthor Commented:
Great tip!  Thanks Vasto!  It's very much appreciated!
0
 
vastoCommented:
You are welcome!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.