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)

where Calculated col = Today's Date.

I am out of logic to continue this. Appreciate help.


You could add a column called "Last Idle days" to record the last time idle days when the status changed to available.

Then use the formula in the idle days column:

=IF(OR(Status="unavailable",Status="Powered-Down"),IF(ISBLANK([Last UnAssigned Date]),"NULL",([Calculated col]-[Last UnAssigned Date]+[Last Idle Days])),0)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.