Solved

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

Posted on 2015-01-13
6
95 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
Comment Utility
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 34

Assisted Solution

by:James0628
James0628 earned 166 total points
Comment Utility
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
Comment Utility
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 34

Expert Comment

by:James0628
Comment Utility
> or in James method

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

 James
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
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 …
This video discusses moving either the default database or any database to a new volume.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now