Solved

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

Posted on 2015-01-13
6
105 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
  • 2
6 Comments
 
LVL 22

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 100

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

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

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. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

749 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