0

I am calculating the number of days a task is aging.

This works great:

=DATEVALUE(TEXT(NOW(),"mm/dd/yyyy")) - DATEVALUE(TEXT([Created],"mm/dd/yyyy"))

However, I need the counting to STOP when the Completed Date is entered, so code only counts as long as COMPLETED_DATE="".

I would also like to exclude weekends.

Thank you!

  • I tried the formula that you posted and it actually computed the correct aging count. However when you input the completion date it will count including "from the received date" and not after it. So for example: if the request was received 09/25 the RCT count appears 3 (which is correct). Then if you input completion date at 09/30 it will count 4 (which it will count the from 25th, 26th, 27th and 30th). Anything you can assist me with this query? Thank you...v – England Ken Fernandez Sep 30 '19 at 15:23
2

Try following formula, Hope it will help you.

=IF(ISBLANK([Completed Date]),IF(ISERROR(DATEDIF([Created],NOW(),”d”)),””,(DATEDIF([Created],NOW(),”d”))+1-INT(DATEDIF([Created],NOW(),”d”)/7)*2-IF((WEEKDAY(NOW())-WEEKDAY([Created]))<0,2,0)-IF(OR(AND(WEEKDAY(NOW())=7,WEEKDAY([Created])=7),AND(WEEKDAY(NOW())=1,WEEKDAY([Created])=1)),1,0)-IF(AND(WEEKDAY([Created])=1,(WEEKDAY(NOW())-WEEKDAY([Created]))>0),1,0)-IF(AND(NOT(WEEKDAY([Created])=7),WEEKDAY(NOW())=7),1,0)),IF(ISERROR(DATEDIF([Created],[Completed Date],”d”)),””,(DATEDIF([Created],[Completed Date],”d”))+1-INT(DATEDIF([Created],[Completed Date],”d”)/7)*2-IF((WEEKDAY([Completed Date])-WEEKDAY([Created]))<0,2,0)-IF(OR(AND(WEEKDAY([Completed Date])=7,WEEKDAY([Created])=7),AND(WEEKDAY([Completed Date])=1,WEEKDAY([Created])=1)),1,0)-IF(AND(WEEKDAY([Created])=1,(WEEKDAY([Completed Date])-WEEKDAY([Created]))>0),1,0)-IF(AND(NOT(WEEKDAY([Created])=7),WEEKDAY([Completed Date])=7),1,0)))

Please replace column name accordingly.

| improve this answer | |
  • Hi! Thank you! This worked, but it did count the Created day. – M0nk3ytr0nix3000 Sep 25 '19 at 17:14
  • 1
    This ended up working for me: = IF(ISBLANK([Completed_Date]), DATEVALUE(TEXT(NOW(),"mm/dd/yyyy")) - DATEVALUE(TEXT([Created],"mm/dd/yyyy")), DATEDIF([Created],Completed_Date,"D")-IF(WEEKDAY(Completed_Date)=7,FLOOR((DATEDIF([Created],Completed_Date,"D")+WEEKDAY([Created]))/7,1)*2,FLOOR((DATEDIF([Created],Completed_Date,"D")+WEEKDAY([Created]))/7,1)*2+1)+IF(WEEKDAY([Created])=7,2,1) – M0nk3ytr0nix3000 Sep 25 '19 at 17:14
  • I think somehow similar to this resolution it will solve my problem. I am trying to calculate as well the aging count of a request. I have 5 columns namely Type of Request, Date Received, Owner, RCT, Completion date. And I want to calculate the aging count (RCT) from the date it was received and it will increment each day until it will be closed and not excluding WEEKENDS to the aging count (RCT). Please help.. Thanks – England Ken Fernandez Sep 30 '19 at 13:41
  • I tried the formula that you posted and it actually computed the correct aging count. However when you input the completion date it will count including "from the received date" and not after it. So for example: if the request was received 09/25 the RCT count appears 3 (which is correct). Then if you input completion date at 09/30 it will count 4 (which it will count the from 25th, 26th, 27th and 30th). Anything you can assist me with this query? Thank you...v – England Ken Fernandez Sep 30 '19 at 15:23
  • Don't understand your question, I have already give me formula which calculate ageing based on active task and completed tasks. If the task is completed it will count from received to completed and if the task is not completed it will count from received to today date. – Hitendra Solanki Oct 1 '19 at 12:18

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.