• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 146
  • Last Modified:

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

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
Grapelady
Asked:
Grapelady
  • 2
3 Solutions
 
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
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
 
James0628Commented:
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
 
mlmccCommented:
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
 
James0628Commented:
> or in James method

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

 James
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.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now