Convert Excel Formula to Sharepoint Calculated formula

Question

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

=IF(OR($F13>=TODAY(),ISBLANK($F13)),0,IF(ISBLANK($G13),NETWORKDAYS($F13,TODAY(),Reference!$E$2:$E$31),NETWORKDAYS($F13,$G13,Reference!$E$2:$E$31)))

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)

Thanks

Answer 1

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

Answer 2

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?