Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Take comma out of year but still keep as date

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?

Former Member
Not what you were looking for? View more on this topic or Ask a question