# Calculated column for date difference taking into account weekends and negatives (Errors #NUM! and #Name?)

I've been fighting this and going around in circles, hopefully someone will be able to help me out.

Issue:

I am trying to create a calculated column (Text) that compares today's date "Now()" with a date column "End Date". All I am looking to do is compare the end date to today's date and give me the difference in a numeric value (be that positive or negative). The tricky bit is that I am also excluding weekends from the count and this appears to be where I am getting stuck.

The first nested IF statement below works perfectly, it shows the date difference and even shows negative numbers (see row 3 in the screenshot below).

The second nested statement (which as you can see I copied from the first) but as you can see from the screenshot I am getting a random number in row 1 (this should be -5) and an error in row 2? I've injected ISERROR to try and resolve the issue but this still isn't working the way I'd like.

IF Statement:

``````=IF(NOT(ISBLANK([Actual Finish Date])),
IF(AND((WEEKDAY([Actual Finish Date],2))<(WEEKDAY([Start Date],2)),
((WEEKDAY([Start Date],2))-(WEEKDAY([Actual Finish Date],2)))>1),
(((DATEDIF([Start Date],[Actual Finish Date],"D")+1))-(FLOOR((DATEDIF([Start Date],[Actual Finish Date],"D")+1)/7,1)*2)-2),
(((DATEDIF([Start Date],[Actual Finish Date],"D")+1))-(FLOOR((DATEDIF([Start Date],[Actual Finish Date],"D")+1)/7,1)*2)))-[Total Days],

IF(AND((WEEKDAY([End Date],2))<(WEEKDAY(Now(),2)),
((WEEKDAY(Now(),2))-(WEEKDAY(Now()[End Date],2)))>1),
(ISERROR((DATEDIF(Now(),[End Date],"D")+1))-(FLOOR((DATEDIF([End Date],Now(),"D")+1)/7,1)*2)-2),
(ISERROR((DATEDIF(Now(),[End Date],"D")+1))-(FLOOR((DATEDIF([End Date],Now(),"D")+1)/7,1)*2)))
)
``````

Screenshot: Rob

UPDATE 1

Thanks for the pointers willman, I've now created a new date column called "TodaysDate" this update daily with a Flow as you suggested.

I've update the original formula to reference the new column:

``````=IF(NOT(ISBLANK([Actual Finish Date])),
IF(AND((WEEKDAY([Start Date],2))<(WEEKDAY([Actual Finish Date],2)),
((WEEKDAY([Start Date],2))-(WEEKDAY([Actual Finish Date],2)))>1),
(((DATEDIF([Start Date],[Actual Finish Date],"D")+1))-(FLOOR((DATEDIF([Start Date],[Actual Finish Date],"D")+1)/7,1)*2)-2),
(((DATEDIF([Start Date],[Actual Finish Date],"D")+1))-(FLOOR((DATEDIF([Start Date],[Actual Finish Date],"D")+1)/7,1)*2)))-[Total Days],

IF(AND((WEEKDAY([End Date],2))<(WEEKDAY([TodaysDate],2)),
((WEEKDAY([End Date],2))-(WEEKDAY([TodaysDate],2)))>1),
(((DATEDIF([End Date],[TodaysDate],"D")+1))-(FLOOR((DATEDIF([End Date],[TodaysDate],"D")+1)/7,1)*2)-2),
(((DATEDIF([End Date],[TodaysDate],"D")+1))-(FLOOR((DATEDIF([End Date],[TodaysDate],"D")+1)/7,1)*2)))
``````

But for some reason I am still getting #NUM! error (row 1) although it is basically the same IF statement as the first one which does return a negative number.

Screenshot: UPDATE 2 - Solved!

Thanks to the help below the working formula is:

``````=IF(NOT(ISBLANK([Actual Finish Date])),
IF(ISERROR(DATEDIF([Start Date],[Actual Finish Date],"d")),"",
(DATEDIF([Start Date],[Actual Finish Date],"d"))+1-INT(DATEDIF([Start Date],[Actual Finish Date],"d")/7)*2-IF((WEEKDAY([Actual Finish Date])-WEEKDAY([Start Date]))<0,2,0)-IF(OR(AND(WEEKDAY([Actual Finish Date])=7,
WEEKDAY([Start Date])=7),AND(WEEKDAY([Actual Finish Date])=1,WEEKDAY([Start Date])=1)),1,0)-IF(AND(WEEKDAY([Start Date])=1,
(WEEKDAY([Actual Finish Date])-WEEKDAY([Start Date]))>0),1,0)-IF(AND(NOT(WEEKDAY([Start Date])=7),WEEKDAY([Actual Finish Date])=7),1,0))-[Total Days],

IF(ISERROR(DATEDIF([End Date],[TodaysDate],"d")),"",
(DATEDIF([End Date],[TodaysDate],"d"))+1-INT(DATEDIF([End Date],[TodaysDate],"d")/7)*2-IF((WEEKDAY([TodaysDate])-WEEKDAY([End Date]))<0,2,0)-IF(OR(AND(WEEKDAY([TodaysDate])=7,
WEEKDAY([End Date])=7),AND(WEEKDAY([TodaysDate])=1,WEEKDAY([End Date])=1)),1,0)-IF(AND(WEEKDAY([End Date])=1,
(WEEKDAY([TodaysDate])-WEEKDAY([End Date]))>0),1,0)-IF(AND(NOT(WEEKDAY([End Date])=7),WEEKDAY([TodaysDate])=7),1,0)))
``````

Thanks all!

• NOW() is not supported in Calculated Columns. It is supported in Default Value and Validation formulas, but not in Calculated Columns. Mar 16 '20 at 14:27
• If you need a calculation to update every day, then you'll need to create a scheduled Flow or workflow to update each day. Mar 16 '20 at 14:29

``````=IF(ISERROR(DATEDIF(RequestedDate,EndDate,"d")),"",(DATEDIF(RequestedDate,EndDate,"d"))+1-INT(DATEDIF(RequestedDate,EndDate,"d")/7)*2-IF((WEEKDAY(EndDate)-WEEKDAY(RequestedDate))<0,2,0)-IF(OR(AND(WEEKDAY(EndDate)=7,WEEKDAY(RequestedDate)=7),AND(WEEKDAY(EndDate)=1,WEEKDAY(RequestedDate)=1)),1,0)-IF(AND(WEEKDAY(RequestedDate)=1,(WEEKDAY(EndDate)-WEEKDAY(RequestedDate))>0),1,0)-IF(AND(NOT(WEEKDAY(RequestedDate)=7),WEEKDAY(EndDate)=7),1,0))