0

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

enter image description here

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

calculated column formula

display

0

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

Result of above formula - Timeline_WeekDays

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.