We help IT Professionals succeed at work.

Relative date formula question:   crystal date parameters

The formula below is supposed to work no matter whether you put in me-1 for month ending, or 11/30/2011.  I am missing one patient on my report and the only thing that seems to be the issue is the date range.  CAn you take a quick look at this formula and see if anything jumps out at you that might make this formula NOT work?  I am also going to give you a formula that does work, but it only works in sequel.

Crystal formula that is missing a patient:
// Accepts a date as a string in mm/dd/yyyy, yyyy-mm-dd, or
// relative (t-45) format. Returns a standard Crystal date.

local numberVar SRange;
local numberVar SOperator;
local stringVar SInterval;
local stringVar STemp;

if IsDate({?EndDate}) then Date({?EndDate})                     //If proper Crystal date was entered, don't change
else
(
    if InStr({?EndDate},"+")>0 then SOperator:=1                //Plus or minus?
    else SOperator:=-1;

    STemp:=Replace({?EndDate},"+","-");                         //Convert + to -, for easier string manipulation

    If NumericText(Mid(STemp,Instr(STemp,"-")+1)) then          //Extract and make sure valid number
        SRange:=toNumber((Mid(STemp,Instr(STemp,"-")+1)))
    else SRange:=0;

    SInterval:=Left(STemp,InStr(STemp,"-"));                    //Extract first part. Ex. "T" or "W"

    if Left(SInterval,1)="T" or SInterval="TODAY" then          //Dates relative to Today
        DateAdd("d",(SOperator*SRange),CurrentDateTime)

    else if Left(SInterval,1)="W" or SInterval="WEEK" then      //Dates relative to this week
        DateAdd("ww",(SOperator*SRange),CurrentDateTime)

    else if Left(SInterval,1)="M" or SInterval="MONTH" then     //Dates relative to this month
        DateAdd("m",(SOperator*SRange),CurrentDateTime)

    else if Left(SInterval,1)="Y" or SInterval="YEAR" then      //Dates relative to this year
        DateAdd("yyyy",(SOperator*SRange),CurrentDateTime)

    else if Left(SInterval,1)="Q" or SInterval="QUARTER" then   //Dates relative to this quarter
        DateAdd("q",(SOperator*SRange),CurrentDateTime)

    else                                                        // Error trap
        CurrentDate
)

Sequel formula that includes that patient:
(pe.APPT_TIME >=
         DATEADD(d, -day(DATEADD(d, -day(getdate()) , datediff(d,0,getdate()))) + 1, (DATEADD(d, -day(getdate()), datediff(d,0,getdate()))))
   and pe.APPT_TIME < DATEADD (d,-DAY(getdate()) + 1,datediff(d,0,getdate())))
Comment
Watch Question

Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
How is the formula being used?

mlmcc
What date do you want ME-1 to give you?

 It's going to subtract 1 month from the current datetime, so today (12/06), it would give you 11/06.  If you want it to give you the end of last month (11/30), then you need to change the "Dates relative to this month" part of the formula.

 James
Becky EdwardsEpic Clarity Developer

Author

Commented:
crud.  I thought me-1 would be month ending minus 1 - meaning 11/30/2011.  What needs changed in the Dates Relative section?
The dates will all be relative to today's date.  For example, W-1 will give you one week ago today, not the beginning or ending of the previous week, and Q-1 will subtract 3 months from today, not give you the beginning or ending of the previous quarter.

 I'm wondering how you want all of those to work.  Here's what they all give you for today:

T-1   12/05/2011 02:21:30 PM
W-1   11/29/2011 02:21:30 PM
M-1   11/06/2011 02:21:30 PM
Y-1   12/06/2010 02:21:30 PM
Q-1   09/06/2011 02:21:30 PM


 James
Oh, and FWIW, you used me-1 as your example, with the "me" meaning "month ending", but your formula doesn't use the "e".  It's just looking at the first letter, so "M-1" (as in my example above), or "Me-1", or "Mary-1", will all give you the same results (subtract 1 month from today's date).

 James
Becky EdwardsEpic Clarity Developer

Author

Commented:
OK.  All those above are ok and I want them to be what you are showing.  But I also want an additional section that says mb-1 and me-1 for first day of last month to last day of last month.  Most of our reports can be built either using t-10 or t-7, like that.  But I especially need the beginning and ending of a month.  Can that be built into this formula?
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
Sure

     if Left(SInterval,2)="me"  then                                              // dates reltive to  month ending
         DateAdd("m",(SOperator*(SRange-1)),CurrentDateTime - day(currentdate))
    else if Left(SInterval,1)="T" or SInterval="TODAY" then          //Dates relative to Today
        DateAdd("d",(SOperator*SRange),CurrentDateTime)

    else if Left(SInterval,1)="W" or SInterval="WEEK" then      //Dates relative to this week
        DateAdd("ww",(SOperator*SRange),CurrentDateTime)

    else if Left(SInterval,1)="M" or SInterval="MONTH" then     //Dates relative to this month
        DateAdd("m",(SOperator*SRange),CurrentDateTime)

    else if Left(SInterval,1)="Y" or SInterval="YEAR" then      //Dates relative to this year
        DateAdd("yyyy",(SOperator*SRange),CurrentDateTime)

    else if Left(SInterval,1)="Q" or SInterval="QUARTER" then   //Dates relative to this quarter
        DateAdd("q",(SOperator*SRange),CurrentDateTime)

    else                                                        // Error trap
        CurrentDate

mlmcc
mlmcc forgot "mb", for the first day of the month.  Also, his "me" calculation doesn't work for adding months (eg. me+1), because he subtracts 1 from the number (SRange).  So, me+1, for example, would give you 11/30/2011.  I assume that it should be 01/31/2012.  If you're not going to use "+" with "me", then maybe it doesn't matter.

 This version of your if statement includes "MB", and should handle - or +.  It appears to work from the little bit of testing I did.

    if Left(SInterval,2)="ME"  then                             //Dates relative to current month ending
         DateAdd ("m",(SOperator*SRange),
          DateTime (DateSerial (Year (CurrentDateTime), Month (CurrentDateTime) + 1, 1) - 1, CurrentTime))

    else if Left(SInterval,2)="MB"  then                        //Dates relative to current month beginning
         DateAdd ("m",(SOperator*SRange),DateTime (Minimum (MonthToDate), CurrentTime))

    else if Left(SInterval,1)="T" or SInterval="TODAY" then          //Dates relative to Today
        DateAdd("d",(SOperator*SRange),CurrentDateTime)

    else if Left(SInterval,1)="W" or SInterval="WEEK" then      //Dates relative to this week
        DateAdd("ww",(SOperator*SRange),CurrentDateTime)

    else if Left(SInterval,1)="M" or SInterval="MONTH" then     //Dates relative to this month
        DateAdd("m",(SOperator*SRange),CurrentDateTime)

    else if Left(SInterval,1)="Y" or SInterval="YEAR" then      //Dates relative to this year
        DateAdd("yyyy",(SOperator*SRange),CurrentDateTime)

    else if Left(SInterval,1)="Q" or SInterval="QUARTER" then   //Dates relative to this quarter
        DateAdd("q",(SOperator*SRange),CurrentDateTime)

    else                                                        // Error trap
        CurrentDate


 FYI, I used DateSerial in the "ME" section because it's more flexible.  If you give it a month or day that would normally be invalid (eg. month 13 or day 36), it adjusts the result to compensate.  So, for today for example, the arguments would be DateSerial (2011, 12 + 1, 1), but it would translate month 13 into January of 2012, so that would give you 01/01/2012.  Then I subtract 1 from the date to get the last day of this month, 12/31/2011, and use DateAdd to add/subtract months from that.

 Also, in the "MB" and "ME" sections I used CurrentTime to include the time with the calculated date, simply because you're including the time (by using CurrentDateTime) everywhere else.  If you don't need/want the time with those dates, you can remove the DateTime and CurrentTime functions.

 James
I just thought of a potential problem with the "me" calculation (my version and mlmcc's).

 Should me-X always give you the last day of the calculated month?  mlmcc's version would give you 10/30/2011 for me-2, because it's based on the last day in the previous month, which is November in this case, so that's the 30th.  My version would give you 10/31 this month, but if you used me-1 in November, you'd get 10/30, because my version is based on the last day in the current month (November in this case).

 If you want me-X to give you the last day of the calculated month, the following seems to work:

    if Left(SInterval,2)="ME"  then                             //Dates relative to current month ending
         DateAdd ("m",(SOperator*SRange)+1,DateTime (Minimum (MonthToDate), CurrentTime)) - 1


 James
Becky EdwardsEpic Clarity Developer

Author

Commented:
You guys are really genious!  I had to give more points to James if only for the fact that the 31st day of the month has always given me fits!!!!!  I am going to apply all of this to my report and I am confident it will work.  Having worked with what I call Interpreted Start and End Dates for three years now, I understand just enough to be dangerous.  

Thank you for taking the time, James, to explain it all to me and I am sure others for years to come.
Becky EdwardsEpic Clarity Developer

Author

Commented:
Sorry guys I must not be doing something right.  I tried to incorporate your suggestions into my report but the formula keeps erroring out.  What is wrong with my version?

// Accepts a date as a string in mm/dd/yyyy, yyyy-mm-dd, or
// relative (t-45) format. Returns a standard Crystal date.

local numberVar SRange;
local numberVar SOperator;
local stringVar SInterval;
local stringVar STemp;
local numberVar nYear;        
local numberVar nMonth;
local dateVar theDate;

if IsDate({?EndDate}) then Date({?EndDate}) //If proper Crystal date was entered, don't change
else

if Left(SInterval,2)="ME" then //Dates relative to current month ending
DateAdd ("m",(SOperator*SRange)+1,
DateTime (Minimum (MonthToDate), CurrentTime))–1

else if Left(SInterval,2)="MB" then //Dates relative to current month beginning
DateAdd ("m",(SOperator*SRange),DateTime (Minimum (MonthToDate), CurrentTime))

else if Left(SInterval,1)="T" or SInterval="TODAY" then //Dates relative to Today
DateAdd("d",(SOperator*SRange),CurrentDateTime)

else if Left(SInterval,1)="W" or SInterval="WEEK" then //Dates relative to this week
DateAdd("ww",(SOperator*SRange),CurrentDateTime)

else if Left(SInterval,1)="M" or SInterval="MONTH" then //Dates relative to this month
DateAdd("m",(SOperator*SRange),CurrentDateTime)

else if Left(SInterval,1)="Y" or SInterval="YEAR" then //Dates relative to this year
DateAdd("yyyy",(SOperator*SRange),CurrentDateTime)

else if Left(SInterval,1)="Q" or SInterval="QUARTER" then //Dates relative to this quarter
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
You didn't post the full formula.

I don't see anything wrong but I do get an error when I paste the formula (minus the last line) into a report.

I can fix it by deleting the -1 and readding it on this line
DateAdd ("m",(SOperator*SRange)+1,
DateTime (Minimum (MonthToDate), CurrentTime))–1

mlmcc
I think mlmcc is correct.  These are your lines for the "ME" calculation:

DateAdd ("m",(SOperator*SRange)+1,
DateTime (Minimum (MonthToDate), CurrentTime))–1

 That may look correct, but that "minus" at the end is not actually a "-".  It's some other character.  If you copied that from your formula and pasted it here, that's your problem (or at least part of it).

 FWIW, I spotted it right off because it just doesn't look the same in the browser.

 Here's your version (combined into one line):

DateAdd ("m",(SOperator*SRange)+1, DateTime (Minimum (MonthToDate), CurrentTime))–1

 Here's mine:

DateAdd ("m",(SOperator*SRange)+1,DateTime (Minimum (MonthToDate), CurrentTime)) - 1

 The difference is quite obvious to me, but it may depend on your browser and language settings.

 Also, if you copy your line and paste it into an empty CR formula, and then have CR search the formula for a minus character, it won't find anything, which just confirms that that is not actually a minus character.

 James
Becky EdwardsEpic Clarity Developer

Author

Commented:
Wow, I would have NEVER caught that.  I did some copy pasting of the formula to get it over into my other network, and that's probably what did it.  I will retry.  Thank you!!!!!
I've actually seen that before here.  I don't know how/why it happened, but someone ended up with some character that looked like a minus, but was something else.  That probably helped me spot it.

 James