# Calculate the percent of Workday time used

• 0
• Question
• Updated 4 years ago

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

• 324 Points

Posted 4 years ago

• 0

QuickBaseCoach App Dev./Training, Champion

• 67,760 Points
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])))
• 324 Points
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.

QuickBaseCoach App Dev./Training, Champion

• 67,760 Points

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

1-((1+WeekdaySub([Phase End Date],Today())))/(1+WeekdaySub([Phase End Date],[Testing Start Date])))
• 324 Points
Also, how do i nest another If Statement if my Testing Start Date is greater than Today which makes that % less than 0?
• 324 Points
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..

QuickBaseCoach App Dev./Training, Champion

• 67,760 Points
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])))
• 324 Points
once again!  Greatness.  Wish i could buy you a beer or two!  :smile:  Thank you!

QuickBaseCoach App Dev./Training, Champion

• 67,760 Points
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".