Forum Discussion

DanZ24's avatar
DanZ24
Copper Contributor
Dec 30, 2020
Solved

Sharepoint List - Set Calculated Field to Null or Blank

Hello all,   I'm using the below formula to calculate the number of days between the start and end date of a task: =(DATEDIF(DateStarted,DateCompletedorClosed,"D"))-INT(DATEDIF(DateStarted,DateCom...
Lists
SharePoint Online
  • stormin_30's avatar
    stormin_30
    Jan 02, 2021

    Hi DanZ24,

     

    You can test the length of DateCompletedorClosed. If it is 0 then provide a default value like blank(" ").

     

    =IF(LEN(DateCompletedorClosed)=0," ",(DATEDIF(DateStarted,DateCompletedorClosed,"D"))-INT(DATEDIF(DateStarted,DateCompletedorClosed,"D")/7)*2-IF(WEEKDAY(DateCompletedorClosed)<WEEKDAY(DateStarted),2,IF(OR(WEEKDAY(DateCompletedorClosed)=7,WEEKDAY(DateStarted)=1),1,0))+1)

    I hope this helps.

     

    Norm

stormin_30's avatar
stormin_30
MVP
Jan 02, 2021

Hi DanZ24,

 

You can test the length of DateCompletedorClosed. If it is 0 then provide a default value like blank(" ").

 

=IF(LEN(DateCompletedorClosed)=0," ",(DATEDIF(DateStarted,DateCompletedorClosed,"D"))-INT(DATEDIF(DateStarted,DateCompletedorClosed,"D")/7)*2-IF(WEEKDAY(DateCompletedorClosed)<WEEKDAY(DateStarted),2,IF(OR(WEEKDAY(DateCompletedorClosed)=7,WEEKDAY(DateStarted)=1),1,0))+1)

I hope this helps.

 

Norm

Resources