 # 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

2 Replies

# Re: Calculated Column - if statements

@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)

# Re: Calculated Column - if statements

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