I have a simple SharePoint item list . Consider this specific spreadsheet for an example:
A1:(Last UnAssigned Date) contains a date.
B1: (Status) Status contains a status Available/unavailable/.
C1: (Idle Days) Idle Days has nothing and should get result.We have manually entered the lastUnAssigned date .
I need result in C1 (Idle Days) where it should be the cumulative days of a row with status unAvailable. if status=unavailable for 20 days, Idle days should be 20, in case if status changed to available for next 10 days Idle days should be 20 and should increment when the state changed to unavailable.
What I Tried: I tried multiple methods to work, tried creating workflow,calculated formulas but failed. I have accomplished one occurrence where I created a column called Today with current days date and subtracted both to get the first occurrence of Idle days.
=IF(OR(Status="Available",Status="Powered-Down"),IF(ISBLANK([Last UnAssigned Date]),"NULL",([Calculated col]-[Last UnAssigned Date])),0)
col = Today's Date.
I am out of logic to continue this. Appreciate help.