lookup column in calculated column formula

Question

I have a Lookup column(Discountfirst) which has Number. I also have another Calculated column(Finaldate) with Resulttype DateTime. One more column(DateofReceipt) in Date.

My Calculated column formula looks like this

=(DateofReceipt)+(Discountfirst). 

I get an error

"One or more column references are not allowed, because the columns are defined as a data type that is not supported in formulas".

I googled and came to know I can't use Lookup Column in Calculated field.

Any suggestion how can do this ?

Answer 1

Note:

in June 2017, Microsoft disabled the use of JavaScript in a Calculated Column

That means given answers may not apply for newer SharePoint versions

For long explanation and work arounds see:
June 13th 2017 Microsoft blocked handling HTML markup in SharePoint calculated fields - how to get the same functionality back

---

Original answer:

The Lookup info you are after is actually available in the Context object once the View has loaded, so you can access it with eithe CSR or....

Stuff some Javascript in a Calculated Column... less lines compared to CSR.. but difficult to debug if you don't get the syntax right.

You will have to use your own internal fieldname DiscountFirst

Create a calculated column and paste the Formula, then set the datatype to Number so it executes the HTML

=" <img src=""/_layouts/images/blank.gif"" onload=""{"
    &"var TR=this.parentNode.parentNode.parentNode,"
    &"    item=ctx.ListData.Row[TR.rowIndex-1];"
    &"console.log(item);"
&"}"">"

Look at the F12 console and check the exact spelling for your required fields

Then change the Formula to something like:

=" <img src=""/_layouts/images/blank.gif"" onload=""{"
    &"var TR=this.parentNode.parentNode.parentNode,"
    &"    item=ctx.ListData.Row[TR.rowIndex-1],"
    &"    discount=item.Vendor_x003a_Discount,"
    &"    price=item['Price.'];"
    &"console.log(discount,price,item);"
    &"this.parentNode.innerHTML=discount*price;"
&"}"">"

explanation

The onload function is triggered once the blank image is loaded.
The Table Row has the rowIndex.. but the first table row in the View Table is the Header row
The colon in Vendor:Discount is escaped when the Lookup was created
Price is a currency and a string notation with your locale, but Microsoft provides it as value is well.. only with a weird notation with a dot at the end (which can not be used as valid Javascript, so we access it by the string value

at the end the whole IMG tag is overwritten with the calculation

Only drawback of stuffing HTML/Javascript in a Calculated Column is it will show up as text in Forms (can be hidden) and on Email Alerts

Details at www.ViewMaster365.com/#How

iCC

Answer 2

The next option with you is to create a Workflow using SharePoint Designer.

Run the workflow on item create and item update.

1. Change the field FinalDate to a DateTime
2. Create a new workflow and attach it to the List in question
3. In the workflow using DateFunction you can add the value into a variable
4. Use Update Current Item activity and update field FinalDate