calculating progress of Time or Date

[lezawang]

Hi
I want to calculate the progress. I have Start and End date and also Start and End time. I want to find the progress as of today and time now.

So I used conditional formatting --> new --> based on cell value -->bar
(please see attached excel file).

for date
A2= Start date: 1/1/2019
C2= End date: 12/12/2019

So the bar Min value set to A2 and Max value set to C2. The value of the cell set to =today()
Now I want to calculate the progress in term of % as of today. So I wrote this formula

= today()/(C2-A2)

But the result is not correct

I did the same thing for time

A9= Start time: 7:00 am
C9= End time: 4:00 pm

progress = time(hour(now()),minute(now(),second(now())/ (C9-A9)

But again the result is not correct. Can you please show me how to find the progress in this 2 cases? Thank you very much.


https://drive.google.com/file/d/11Zxq7xn06v9f2Et7Ak6AA8xLZrbenXP2/view?usp=sharing

 

[shg]

= (today() - A2) / (C2 - A2)

 

[lezawang]

Yes it works thank you so much. would you kindly explain to me why subtracted today()-start time or for the second example time()-start time? thank you so much for the help really.

 

[shg]

Basic linear interpolation:

Pct Complete = (now - begin) / (end - begin)

The period of performance is (end - begin) and the current state is (now - begin).

If you get on I-35 in Austin at mile marker 233 headed to Dallas at mile marker 450, then at mile marker 337 in Waco (or Whacko), you are

(337 - 233) / (450 - 233) ~ 48% complete.