totext (date) 1

[nb4884]

Hi All,

I have a formula @date in which i have converted date to a sting:

ie @date= if Stage.query1 = 'A' then totext({Finish date.query1},'MM/dd'yyyy') else ""

Now my concern is in my other formula I have to pick only month ie MM and compare it with months in numbers to get the respective quarter.

something like
if @date,MM in '01,02,03' then 1 else
if @date,MM in '04,05,06' then 2

but I am getting an error..

Can someone suggest me the formula for this?

Thanks

 

[MCuthill]

nb4884,

Something to the effect of:

IF Month(Date({@Date})) in [1,2,3] THEN 1 ELSE
IF Month(Date({@Date})) in [4,5,6] THEN 2 ELSE 
etc... 
etc...

should work.

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[nb4884]

Thanks Mike,

But this gives me an error "Bad Date Format String" highlighting
Date({@Date})

Pls Advice

 

[MCuthill]

nb4884,

I would think this is due to the ToText() performed in your original formula. The Date() function requires a date-formatted field -- perhaps referencing your original field will correct the issue. (I wondered if there may be trouble using the formula field...)

Is {Finish date.query1} a Date/Time field? If so, you don't really need to use the ToText to format, but can use the formatting options in Crystal.

Either way, if {Finish date.query1} is a Date/Time field, replace {@Date} with it in my posting above.

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[MCuthill]

Wait!!

You have "" as the "else" in {@Date}. These could also be the issue.

Another test to try is:

IF {@Date} <> "" THEN
(
IF Month(Date({@Date})) in [1,2,3] THEN 1 ELSE
IF Month(Date({@Date})) in [4,5,6] THEN 2 ELSE 
etc... 
etc...
) ELSE ""

The last line could also read: ELSE "No Date" or something to that effect if more meaningful.

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[nb4884]

Yes Finish Date is a datetime field. But for a project I have number of stages and their respective dates. The stages are strings so in my report i create a @date formula so it points to particular stage (lets say A) and in the formula i had to convert Finish date which is a datetime to string using totext. If I dont use a totext function there itself then it gives me an error saying string reqd here.

Now for the stage A i have @date which is a string , for my quarter formula I need to pick the month from the Finish date which is in string format.

And if I replace @date with Finishdate.query1 as suggested above , how would the formula know if the stage is still A?

Pls suggest

 

[MCuthill]

nb4884,

{@Date} states a String is needed because the result of the ELSE is a string (""). All results of an IF must be of the same type.

If I follow your post, you need the Quarter only when Stage A is identified. Using your {@Date} formula, the month is always going to be the first 2x characters. Left({@Date},2) will return the first two characters.

IF {@Date} <> "" THEN
(
IF Left({@Date},2) in ["01","02","03"] THEN "1" ELSE
IF Left({@Date},2) in ["04","05","06"] THEN "2" ELSE
IF Left({@Date},2) in ["07","08","09"] THEN "3" ELSE
IF Left({@Date},2) in ["10","11","12"] THEN "4"
)
ELSE ""

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[nb4884]

Thanks a lot Mike ***** five stars to you

Your second suggestion worked very well. I didnt see earlier may be coz I was replying at the same time.

Below option works great :
Do you recommend me changing my @date formula to have something other than ""


IF {@Date} <> "" THEN
(
IF Month(Date({@Date})) in [1,2,3] THEN 1 ELSE
IF Month(Date({@Date})) in [4,5,6] THEN 2 ELSE
etc...
etc...
) ELSE

 

[MCuthill]

nb4884,

You are welcome, happy to help.

There is no reason I can think of that you "should" change {@Date} - it comes down to preference. I don't personally like dealing with empty strings, but because you are working with a Date field, something like Jan 1, 1900 is likely misleading versus the empty string.

In my reporting, I am dealing with numbers most of the time and would use a zero in place of "". Zero isn't really an option with dates.

Just a matter of remembering what the empty {@Date} result reflects when using the report!

Cheers, have a great weekend!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[nb4884]

Thanks

 

[lbass]

Or you could have a used a formula like this:

if Stage.query1 = 'A' then
datepart("q",{Finish date.query1})

This would return the quarter, and 0 would be the default (you don't need to explicitly add it), and you could format the formula to suppress if zero, if you don't want to display a zero result.

-LB