percentage of days passed between 2 dates

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

I have a start date and end date and want to calculate what percentage of days has passed since the start date.

I will also have to check whether the start date has been reached and if passed the end date it would be 100% and not keep counting.

I have a formula numeric field that calculates the total number of days between start and finish   ToDays([End Date]-[Start Date])

I have a formula numeric field that needs to calculate number of days passed since start but it needs modifying to encompass if the start date has been reached otherwise its 0.    ToDays(Today()-[Start Date])

Then I created another formula numeric % field to calculate the difference between these 2 but I get some completely weird results.

HELP!

Thanks...  Mick

Photo of Insight

Insight

  • 0 Points

Posted 3 years ago

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

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
Not tested but try this as a formula numeric field set to display in Percentage.

var number Span =   ToDays([End Date]-[Start Date]) +1;
var number Elapsed =   ToDays(Today()-[Start Date]) +1;

MIN(1, $Elapsed / $Span)
Photo of Insight

Insight

  • 0 Points
Its nearly there...   If I remove the +1 then I get the correct % based on my manual calculation.
However, for something that has not yet reached the start date I'm getting -X%.  I would rather this was 0% ??
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
Good point

Try this

MAX(MIN(1, $Elapsed / $Span),0)
Photo of Insight

Insight

  • 0 Points
Thats it.   Do you know a good resource I could use to learn this?

Thanks again Mark!

This conversation is no longer open for comments or replies.