Solved

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

Posted on 2015-01-13
6
115 Views
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.
Test-SampleDOH-60-1st-of-month-formula.r
Copy-of-Copy-of-DOH-60-1st-of-month-form
0
Comment
Question by:Grapelady
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
6 Comments
 
LVL 23

Accepted Solution

by:
Ido Millet earned 168 total points
ID: 40548144
IF day({EMGS_GENERAL_STATUS.HIRE_DATE} + 60) = 1 Then {EMGS_GENERAL_STATUS.HIRE_DATE} + 60 Else
DateAdd("m",1,DateSerial(Year({@Hire Date + 60}), Month({@Hire Date + 60}),1))
0
 
LVL 35

Assisted Solution

by:James0628
James0628 earned 166 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


 James
0
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 166 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))

mlmcc
0
 
LVL 35

Expert Comment

by:James0628
ID: 40549760
> or in James method

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

 James
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

691 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