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:
Any help gratefully received
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:
Any help gratefully received. Rob
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!
