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 pr...
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

Resources