# calculated coluum formula error

Escalation status to be calculated as per below: Conditions: 1)if the status is "ETA Slipped" and Original ETA is > 15 days and Revised ETA is null Escalation status should be "ETA slipped beyond 15 days" 2)if the status is "ETA Slipped" and Original ETA is > 15 days and Revised ETA is < 15 days Escalation status should be "ETA slipped within 15 days" 3)if the status is "ETA Slipped" and Original ETA is > 15 days and Revised ETA is >15 days Escalation status should be "ETA slipped beyond 15 days" 4)if the status is "ETA Slipped" and Original ETA is > 30 days and Revised ETA is null Escalation status should be "ETA slipped beyond 30 days" 5)if the status is "ETA Slipped" and Original ETA is > 30 days and Revised ETA is >30 days Escalation status should be "ETA slipped beyond 30 days"

I am able to get this in the excel as below:

``````IF([Status]<>"ETA Slipped",[Status],IF(MAX([Original ETA]:[Revised ETA])>today(),"On track",IF(SUM(MAX([Original ETA]:[Revised ETA]),30)<today(),"ETA Slipped beyound 30 days",IF(SUM(MAX([Original ETA]:[Revised ETA]),15)<Today(),"ETA Slipped beyound 15 days","ETA Slipped within 15 days"))))
``````

But this is not working in sharepoint calculated formula. Pls guide me.

• The `MAX` function is called with comma-separated parameters, not colon ":". Such as `MAX([Column1], [Column2])`. I do believe it works with Dates though, just numbers. support.office.com/en-us/article/… – willman Jan 23 '20 at 18:29
• Awesome! This works!! thanks a lot. – Sai Dinesh Jan 29 '20 at 11:38

## 2 Answers

I don't think `Today` works in calculated formulas:

How to use Today and Me in Calculated column

The first problem, SharePoint does not have the concept of column "ranges" like Excel does. So SharePoint's version of the `MAX` function is called with comma-separated parameters for each column you want to compare. Such as `MAX([Column1], [Column2], [Column3], [etc...])`.
The second problem is the comparison of dates. Technically, SharePoint's `MAX` function only supports number datatypes, not Dates. Luckily, casting a Date to an Int gives you an accurate comparison, for example `MAX(INT([Original ETA]), INT([Revised ETA]))`.
The third issue is created by the fact that we have to convert the dates to numbers. Normally, we can add days to dates, and do comparisons like `[Date1]>[Date2]`, but if we convert the dates inside our Max function to numbers, then our date adds have to happen before the conversion, and comparisons have to be against similarly converted dates.

So the SharePoint version of your formula should be something like: `IF([Status]<>"ETA Slipped",[Status],IF(MAX(INT([Original ETA]), INT([Revised ETA]))>INT(today()),"On track",IF(SUM(MAX([Original ETA]:[Revised ETA]),30)<today(),"ETA Slipped beyound 30 days",IF(SUM(MAX([Original ETA]:[Revised ETA]),15)<Today(),"ETA Slipped beyound 15 days","ETA Slipped within 15 days"))))`