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,DateCompletedorClosed,"D")/7)*2-IF(WEEKDAY(DateCompletedorClosed)<WEEKDAY(DateStarted),2,IF(OR(WEEKDAY(DateCompletedorClosed)=7,WEEKDAY(DateStarted)=1),1,0))+1

 

Formula works great.  The only problem is when I have a row with a start date, but no end date yet, it returns #NAME? in the calculated field. I'd really like to have the field remain blank until the end date is submitted.  

 

Is there a way to accomplish this?

 

Thanks in advance for your time.

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