Take comma out of year but still keep as date

[Former Member]

Hello all!

I am attempting to create a variable which returns Net Commission for a year based on prompt entry. So far I have 3 versions of the formula, none of which are correct...

First version is: =[Net Commission Usd] Where([Year]=Year(ToDate(UserResponse("Enter Sysdate");"M/dd/yyyy HH:mm:ss a")))

The issue with this version is that while [Year] may be 2015 for instance, the second half of the equation returns 2,015 thereby making the Net Commission show nulls everywhere since the years dont match.

The other 2 versions are a result of me trying to resolve this issue..

=[Net Commission Usd] Where([Year]=FormatNumber(Year(ToDate(UserResponse("Enter Sysdate");"M/dd/yyyy HH:mm:ss a"));"####"))

returns the following error: "The expression or sub-expression at position 35 in the '=' function uses an invalid data type."

which led me to trying

=[Net Commission Usd] Where([Year]=FormatDate(Year(ToDate(UserResponse("Enter Sysdate");"M/dd/yyyy HH:mm:ss a"));"yyyy"))

instead since I assumed the issue was with FormatNumber converting the date to a number thereby not matching datatypes with [year] but this returns the error "The expression or sub-expression at position 46 in the 'FormatDate' function uses an invalid data type."

Anyone please advise on how to make this function work?

[amitrathi239]

Hi,

Use this. Create  V Year variable.

V Year=ToDate(UserResponse("Enter Sysdate");"M/dd/yyyy HH:mm:ss a")

Right click on this and Format Number->Date Time->Custom & define the format yyyy.

With this you will get the Year value in the date format.

Final one

=[Net Commission Usd] Where([Year]=[V Year])

Amit

[Former Member]

Vlad,

Year might be in string format.

Can you try the below formula.

=FormatDate(ToDate(UserResponse("Enter Sysdate");"M/dd/yyyy HH:mm:ss a");"yyyy")

=[Net Commission Usd] Where([Year]=FormatDate(ToDate(UserResponse("Enter Sysdate");"M/dd/yyyy HH:mm:ss a");"yyyy"))

-Sri Harsha