Calculated column difference between two dates considering Today

Question

I have a calculated column to determine the number of days between Introduction call date and Today's date. It works fine as long as the introduction call date is less or equal to Today's date but getting a error when it is above today. Please help with the correct formula.

The formula that I use is below and 27 days is the target days, whereby the timeline starts at -27 decrements each day.

=IF([Implementation Status]="COMPLETE",(DATEDIF([Introduction Call date],[Status Date],"D"))-INT(DATEDIF([Introduction Call date],[Status Date],"D")/7)*2-IF(WEEKDAY([Status Date])<WEEKDAY([Introduction Call date]),2,IF(OR(WEEKDAY([Status Date])=7,WEEKDAY([Introduction Call date])=1),1,0))+1-(27),(DATEDIF([Introduction Call date],[Today's Date],"D"))-INT(DATEDIF([Introduction Call date],[Today's Date],"D")/7)*2-IF(WEEKDAY([Today's Date])<WEEKDAY([Introduction Call date]),2,IF(OR(WEEKDAY([Today's Date])=7,WEEKDAY([Introduction Call date])=1),1,0))+1-(27))

Answer 1

Your formula seems incorrect. If you notice the difference in dates

9/1/2021 - 9/21/2021 = -20 but your screenshot is showing -19.

Try using this formula below

=IF(InductionCallDate>TodayDt,DATEDIF(TodayDt,InductionCallDate,"d"),CONCATENATE("-",DATEDIF(InductionCallDate,TodayDt,"d")))

Answer 2

To get dates difference for Weekdays only use the following formula

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

By looking at the screenshot you shared above, how you calculate the weekdays between

9/9/21 - 9/22/21 as -17 ? ( shouldnt it be -10 ?? )

my formula and even checking the calendar ( assuming Weekdays ( Monday to friday ) it returns me -10.

Here is the screenshot , I applied this formula on column Timeline_Weekdays