Crystal question:  1st of Month after 60 days, more definition on formula

Posted on 2015-01-13
Medium Priority
Last Modified: 2015-02-20
I had asked for a solution to a formula to find the 1st of the month after 60 days a while back and have used it, see attached crystal report.  I have found that if the 60 days fall on the 1st of the month then the formula pushes out to the next 1st of the month date, see highlighted areas of the Excel sheet.  How can I correct the formula so if 1st of the month coincides or directly follows the 60 days it will post the most current 1st of the month.
Question by:Grapelady
  • 2
LVL 23

Accepted Solution

Ido Millet earned 672 total points
ID: 40548144
DateAdd("m",1,DateSerial(Year({@Hire Date + 60}), Month({@Hire Date + 60}),1))
LVL 35

Assisted Solution

James0628 earned 664 total points
ID: 40548636
It's a fairly minor thing (and I'm not looking for any points), but I'd use {@Hire Date + 60} in the If too.

IF day({@Hire Date + 60}) = 1 Then {@Hire Date + 60} Else

LVL 101

Assisted Solution

mlmcc earned 664 total points
ID: 40548916
Since the only problem occurs when the date 60 days from now is the first how about using 59  rather than 60

DateAdd("m",1,DateSerial(Year({EMGS_GENERAL_STATUS.HIRE_DATE} + 59), Month({EMGS_GENERAL_STATUS.HIRE_DATE} + 59),1))

or in James method

DateAdd("m",1,DateSerial(Year({@Hire Date + 60} - 1), Month({@Hire Date + 60} - 1),1))

LVL 35

Expert Comment

ID: 40549760
> or in James method

 For the record, Ido posted that, not me.  :-)


Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses
Course of the Month14 days, 7 hours left to enroll

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question