I'm having difficulty converting this Excel Formula to Sharepoint calculated formula. Hoping someone can help.


F13 = Requisition Open Date G13 = Requisition Closed Date Reference!$E$2:$E$31 = Holiday Dates

I've created this in SharePoint for DAYS OPEN which works but doesn't take into account if Requisition Open Date is Blank. It will still calculate the column and show a number like 31,636:

=IF(ISBLANK([Candidate Start Date]),DATEDIF([Requisition Open Date],TodayDate,"D")-IF(WEEKDAY(TodayDate)=7,FLOOR((DATEDIF([Requisition Open Date],TodayDate,"D")+WEEKDAY([Requisition Open Date]))/7,1)*2,FLOOR((DATEDIF([Requisition Open Date],TodayDate,"D")+WEEKDAY([Requisition Open Date]))/7,1)*2)+IF(WEEKDAY([Requisition Open Date])=7,2,1),[Candidate Start Date]-[Requisition Open Date]+1)


2 Answers 2


Unfortunately, SharePoint formatted columns don't support your scenario.

To be more specific, you can't use ranges. For example: $E$2:$E$31 is a range. Also, you can't use the FLOOR function. Quote:

Microsoft SharePoint Foundation formulas for calculated fields are based on Microsoft Excel functions and syntax. However, Microsoft supports only those functions mentioned on this page for use in SharePoint Foundation calculated fields. For example, the Excel function MID is not supported.

Read more on Calculated Field Formulas on docs.microsoft.com

  • Denis, Thanks very much. I was afraid of that. I'll look at another way to do it then. Apr 4, 2021 at 15:31
  • If my answer helped you, can you accept it? Apr 4, 2021 at 15:33

FLOOR is supported in SharePoint: https://support.microsoft.com/en-us/office/floor-function-5168a039-9501-4e02-ac52-83914290ac55

31,636 is a date serial number. 31,636 = 8/12/1986 Format the column as date to see the date value.

Otherwise, is the rest of the formula doing what you need?

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.