Calculated Column - if statements

New Contributor

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

 

2 Replies

@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

 

WorkingDays3.png

 

 

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

@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