Forum Discussion

wallace1530's avatar
wallace1530
Copper Contributor
Jan 04, 2023

Calculated Column - if statements

Hi everyone, 

 

I am hoping someone can help, i am setting up a SharePoint list to replace an existing excel doc. 

 

I need to create a column that calculates the number of days completed in a project; the excel formula is; 
=IF(NOW()>F2,E2,IF(NOW()<D2,0,NETWORKDAYS([@[Start Date]],NOW(),1)))
F2 is End Date
E2 is Duration (time between start and end date)

D2 is Start Date

 

So if today is after the end date show the duration, if the start date is after today show 0, else tell me the days between the start date and today +1 (to include today). 

I am having issues with the last part of the statement, can anyone help me with writing the correct syntax? 

Many Thanks, Matt

 

developer
  • RobElliott's avatar
    RobElliott
    Silver Contributor
    Jan 04, 2023

    wallace1530 the formula in the calculated column to calculate the number of working days between 2 dates, in my case StartDate and EndDate (including today) with a working week of Monday to Friday, is as follows:

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

     

     

     

    Rob
    Los Gallardos
    Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)

    • wallace1530's avatar
      wallace1530
      Copper Contributor
      Jan 04, 2023

      RobElliott 

       

      Thank you for the advice regard counting the workdays between 2 dates.

       

      I used this formula to count workdays between 2 dates; 

      =(DATEDIF([Start Date],[End Date],"D"))-INT(DATEDIF([Start Date],[End Date],"D")/7)*2-IF((WEEKDAY([End Date])-WEEKDAY([Start Date]))<0,2,0)+1

       

      What i am trying to get is the number of elapased days as our start and end dates can be in the future, so there are 3 elements; 

      if today is after the end date show the duration,

      if the start date is after today show 0,

      else tell me the days between the start date and today +1 (to include today). 

       

      Kindest Regards,

      Matt