0

Escalation status to be calculated as per below:

enter image description here

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.

Sai Dinesh
  • 21
  • 2
  • 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. https://support.office.com/en-us/article/max-function-ffb82196-748c-49d9-916a-86aca191b669 – willman Jan 23 '20 at 18:29
  • Awesome! This works!! thanks a lot. – Sai Dinesh Jan 29 '20 at 11:38

2 Answers2

0

I don't think Today works in calculated formulas:

How to use Today and Me in Calculated column

Mike
  • 12,186
  • 8
  • 41
  • 64
0

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"))))

willman
  • 3,724
  • 1
  • 14
  • 25