Calculate the percent of Workday time used

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

I need to calculate the percent of time used similar to the "Networkdays" function in Excel.  I thought i had the calculation correct until negative numbers popped up.  How do i nest another If Statement if my Testing Start Date is greater than Today? Or better yet, less than 0?  I'm doing something simple wrong with prins in the wrong place.  Is my formula even RIGHT???

Phase Start Date = 7/15/15

Phase End Date = 9/30/15

Testing Start Date = 8/25/15

So, if today is 8/11/15, using the dates above, i've used 36% of my time. 20 workdays between 7/15 and 8/11....and there are 55 total workdays between 7/15 and 9/30.

My formula is below.  

If([Daily NET Planned TCs]=0,0,1-(WeekdaySub([Phase End Date],Today()))/WeekdaySub([Phase End Date],[Testing Start Date]))

Thank you in advance!!

Photo of Jana

Jana

  • 324 Points 250 badge 2x thumb

Posted 4 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,994 Points 50k badge 2x thumb
It's the fence post problem. if you have three fence posts how many fences do you have? Only 2.  ie are you counting posts or fences.



=Networkdays(date, date) counts the fence posts.

Weekday Sub is like when you subtract 5-1=4, but Networkdays would count that as 5 if some one worked from Monday the 1st to Friday the 5th. WeekdaySub would count that as 4.


Not tested but you have to add 1 day to each WeekdaySub result.




If([Daily NET Planned TCs]=0,0,

1-((Days(1)+WeekdaySub([Phase End Date],Today())))/(Days(1)+WeekdaySub([Phase End Date],[Testing Start Date])))
Photo of Jana

Jana

  • 324 Points 250 badge 2x thumb
Thank you!  It says that Days(1) is a duration and the Weekday part isnt so it is throwing an error.

The expression Days(1) on the left hand side of the operator "+" is of type duration while the expression WeekdaySub([Phase End Date],Today()) on the right hand side is of type number. The operator "+" cannot be used with these types of expressions.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,994 Points 50k badge 2x thumb
ya, sorry about that.  We should just be adding 1.

If([Daily NET Planned TCs]=0,0,

1-((1+WeekdaySub([Phase End Date],Today())))/(1+WeekdaySub([Phase End Date],[Testing Start Date])))
Photo of Jana

Jana

  • 324 Points 250 badge 2x thumb
Also, how do i nest another If Statement if my Testing Start Date is greater than Today which makes that % less than 0?
Photo of Jana

Jana

  • 324 Points 250 badge 2x thumb
there will be a time as well where the percentage will get to be over 100% as well so i'd have to program for that as well.  yippie..
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,994 Points 50k badge 2x thumb
Unlike Excel and better than Excel there is no need to "nest"  Nesting just make you crazy with mismatched brackets.

You can just keep listing your conditions and actions lkike this

IF(
[Daily NET Planned TCs]=0,0,
[Testing Start date]>Today(),0,
1-((1+WeekdaySub([Phase End Date],Today())))/(1+WeekdaySub([Phase End Date],[Testing Start Date])))
Photo of Jana

Jana

  • 324 Points 250 badge 2x thumb
once again!  Greatness.  Wish i could buy you a beer or two!  :smile:  Thank you!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,994 Points 50k badge 2x thumb
Nice smiley. Thx.  I think I'm addicted to this forum. I may need to Start a local QuickBase Anonymous chapter. "Hello, my name is QuickBase Coach and I am a QuickBase addict".