Invalid Data Type

building reports webintelligence
1

Why the following function is showing error?

=FormatDate(UserResponse(“Enter value(s) for Payment Date:”);“MM/dd/yyyy”)

It says FormatDate function uses an invalid data type …

I want to remove the time stamp from the date part. The data comes from sap BW

Thanks

Developer_Designer (BOB member since 2009-08-09)

2

Hi,

The result of the UserResponse() function is not a date so you can’t use FormatDate() function. The result is a string. So use SubStr() function to extract first N characters.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

3

Thanks. It works…but…

Let’s say, we extract 9 characters…and it gives the result 9/7/2010. How about for the date: 12/31/2010…so…for this formula will not work for that date ???

Developer_Designer (BOB member since 2009-08-09)

4

You’re looking at a date + time, correct? You need to find the first space which is used to separate the date from the time and use that as the end point for the substring command.

Dave Rathbun :us: (BOB member since 2002-06-06)

5

Thanks. How to get the first space which separtes the date from time ?

Beacuse, if 12/31/2010 and 9/7/2010 have different first space which separates the time from the date

=SubStr(UserResponse(“Enter value(s) for Payment Date:”);"?";“9”)

Developer_Designer (BOB member since 2009-08-09)

6

Hi,
Determine the position of space using Pos function. Like
=Pos(UserResponse(“Enter value(s) for Payment Date:”);" ") will give the position of first space. use this position value in substring to get the string from position number 0 to Position value returned from Pos function.
Here is formula to get the datapart:

=SubStr(UserResponse("Enter value(s) for Payment Date:");0;Pos(UserResponse("Enter value(s) for Payment Date:");" "))

Thanks

forgotUN (BOB member since 2006-12-13)

7

What I usually do in this case is build things in steps. First, I capture the user response in a variable called User Date or something like that.

=UserResponse("Enter value(s) for Payment Date:")

Then I find the first space and call it “First Space” or similar.

=Pos([User Date];" ")

Then I extract the date and format it.

=FormatDate(SubStr([User Date];1;[First Space]-1);"MM/DD/YYYY")

It makes the formulas much easier to read (and maintain) and allows me to debug along the way.

Dave Rathbun :us: (BOB member since 2002-06-06)

8

Why don’t you use ToDate() to convert the string back to Date and then format it using FormatDate()? But give much attention to the date format that you have inside ToDate() function, this should exactly match with your input date format.

Jansi :india: (BOB member since 2008-05-12)

9

Truncate it from backwards (remove the time part, length of time will always be same) and then convert it to date

=Left(UserResponse("Enter value(s) for Payment Date:");length(UserResponse("Enter value(s) for Payment Date:"))-12)

Prashant Purohit :india: (BOB member since 2009-02-18)

10

OK, I must be missing something minor. I’m trying to calculate the number of months between 2 dates entered by the user, so I need to have UserResponse as a date. This variable returns the string “12/1/2010”:

=Left(UserResponse("Begin Date:");Length(UserResponse("Begin Date:"))-12)

I then try to convert it to a date with:

=ToDate(Left(UserResponse("Begin Date:");Length(UserResponse("Begin Date:"))-12);"mm/dd/yyyy")

When I test the formula Webi says it’s correct, but when I display it in the report it returns “#ERROR.”
Am I missing something here?

Norm Rosen :us: (BOB member since 2002-07-10)

11

I think this should work,

=If Length(UserResponse("Enter value(s) for Payment Date:"))>10 Then FormatDate(ToDate(UserResponse("Enter value(s) for Payment Date:");"");"dd/MM/yyyy") Else FormatDate(ToDate(UserResponse("Enter value(s) for Payment Date:");"MM/dd/yyyy");"dd/MM/yyyy")

Thanks,
Zaif

zaif235 :us: (BOB member since 2010-06-15)

12

But FormatDate returns a string, not a Date. I need to see the UserResponses to Begin Date and End Date as Dates so I can use them in the MonthsBetween function.

Norm Rosen :us: (BOB member since 2002-07-10)

13

then you can just convert it to date and don’t format it,

=If Length(UserResponse("Enter value(s) for Payment Date:"))>10 Then ToDate(UserResponse("Enter value(s) for Payment Date:");"") Else ToDate(UserResponse("Enter value(s) for Payment Date:");"MM/dd/yyyy")

Thanks,
Zaif

zaif235 :us: (BOB member since 2010-06-15)

14

But the problem is that I get the #ERROR message when I do a ToDate. This returns the error:

=ToDate(Left(UserResponse("Begin Date:");Length(UserResponse("Begin Date:"))-12);"yyyy/mm/dd")

This also returns the error:

=Month(ToDate(Left(UserResponse("Begin Date:");Length(UserResponse("Begin Date:"))-12);"yyyy/mm/dd"))

I can’t figure out why ToDate isn’t retuning a date.

Norm Rosen :us: (BOB member since 2002-07-10)

15

I think there is a simple way of achievening this.

As weare using free standing cell to display the date, i can use the code

ToDate(UserResponse(“Enter value(s) for Payment Date:”);“MM/dd/yyyy”)
to convert the user response into date, and use format number (right click -> format number) to choose from the templates or use custon template.

Will this work?

bobj_user_a (BOB member since 2010-03-25)

16

Formatting the cell makes no difference. I still get the error.

Norm Rosen :us: (BOB member since 2002-07-10)

17

If userresponse results the date with time stamp then

=ToDate(UserResponse("Enter value(s) for Payment Date:");"")

If results in without time stamp then this will do the trick

=ToDate(UserResponse("Enter value(s) for Payment Date:");"MM/dd/yyyy")

that’s the reason i used If condition in my above post.

Thanks,
Zaif

zaif235 :us: (BOB member since 2010-06-15)

18

Thanks, that did the trick. I guess I didn’t realize that I had to format a timestamp differently from a date.

Norm Rosen :us: (BOB member since 2002-07-10)

19

Cool :+1:

Thanks,
Zaif

zaif235 :us: (BOB member since 2010-06-15)

20

This below work :

Substr(UserResponse ("MyDatePrompt");0;Pos(Substr(UserResponse ("MyDatePrompt");Pos(UserResponse ("MyDatePrompt");"/")+1;Length(UserResponse ("MyDatePrompt")));"/")+Pos(UserResponse ("MyDatePrompt");"/")+4)

Sure ! I tested and approved

:smiley:

[Moderator Edit: Added code formatting - Andreas]

ptitenana_vtq (BOB member since 2015-05-05)