0

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:

enter image description here

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:

enter image description here

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!

2
  • NOW() is not supported in Calculated Columns. It is supported in Default Value and Validation formulas, but not in Calculated Columns.
    – willman
    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.
    – willman
    Mar 16 '20 at 14:29
0

Have you tried this? I found it here, and my tests passed for calculating workdays between two dates.

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

1
  • Thanks Matiur, I thought I had seen everything thread about this on StackExchange but I had missed that one. Problem solved!
    – RobC_CTL
    Mar 19 '20 at 10:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.