I have an InfoPath library/list in SPO. I need to calculate the number of days between two different list items. It's to show the number of days since the last work event. Is there an easy way to do that?


SharePoint Online Calculated column will only provide option to calculate the days for two fields in a same item not for the two items. So a workaround to achieve this is to export the list data to the excel and then using excel formula to calculate the days between dates:

=DATEDIF(A1, B1, "d")

This formula will return the dates count between two item row cells in Excel.

Reference: DateDIF function

| improve this answer | |

You can use InfoPath for this, start via InfoPath a request for the latest created Item in the List and then calculate the difference between the two dates.

| improve this answer | |

There IS a way to reference between two items by calculated column. In this example I will show you how to get the numeric difference between the current item and the item before looking at the values of a column with a number in it.

To see if this solves your issue create a list with some name and two additional columns:

  • Column FIELDNAME as numeric
  • Column DIFF as calculated column set to numeric. Add the following code to it:
="<img src=/_layouts/images/blank.gif onload=""{_spYield(function(){"
    &" var delta=0,TR=GetAncestor(this,'TR'),"
    &" ID=TR.id.split(',')[1],"
    &" ctxRows=ctx.ListData.Row,"
    &" n=getItemIdxByID(ctxRows,ID);"
    &" if(GetAncestor(TR,'TABLE').rows[TR.rowIndex-1].hasAttribute('id')){"
    &"  delta=ctxRows[n].FIELDNAME - ctxRows[n-1].FIELDNAME;"
    &"  this.parentNode.innerHTML=(delta>0?'+':'')+delta;"
    &"  TR.style.backgroundColor=delta>0?'red':'green';"
    &"  }"

Add some items to the list. The DIFF-column will show you the difference between the item and the one before.

What you will have to do is to modify the following math to calcualte the difference between two dates to use it in your list.

ctxRows[n].FIELDNAME - ctxRows[n-1].FIELDNAME;
| improve this answer | |

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.