Website Upgrade Incoming - we're working on a new look (and speed!) standby while we finalise the project

Tips on using this forum..

(1) Explain your problem, don't simply post "This isn't working". What were you doing when you faced the problem? What have you tried to resolve - did you look for a solution using "Search" ? Has it happened just once or several times?

(2) It's also good to get feedback when a solution is found, return to the original post to explain how it was resolved so that more people can also use the results.

Formula for % duration complete

3 replies [Last post]
Fri, 2023-08-11 09:00
Jithin Kambhikanam
User offline. Last seen 8 weeks 3 days ago. Offline
Joined: 22 Mar 2011
Posts: 200
Groups: GPC Qatar, Petro-Chem / Oil & Gas, Planning, Claims & Contracting, TURNAROUND & SHUTDOWN (T/A & S/D)

Hello All

Need your support in excel.

Need to calculate % complete based on duration consumed from dates with an IFS formula

The table

Start

Finish

1-Jul-23

12-Jul-23

15-Aug-23

30-Aug-23

15-Sep-23

10-Jul-23

24-Aug-23

0%

7%

80%

100%

100%

46

 

Duration Complete

3

37

46

46

 

Thanks

Jithin

Top

Replies

Thu, 2023-08-24 16:50
#1
Jithin Kambhikanam
User offline. Last seen 8 weeks 3 days ago. Offline
Joined: 22 Mar 2011
Posts: 200
Groups: GPC Qatar, Petro-Chem / Oil & Gas, Planning, Claims & Contracting, TURNAROUND & SHUTDOWN (T/A & S/D)

Thanks Rodel, Rafael,

 

I am looking for a formula to populate the below table ie when I pull the fosrula from D3 to H3 it automatially gives the % complete based on the dates in D2 to H2.

 

So the Duration based progress % (C3-B3+1) and the % changes based on D2 to H2.

8470
screenshot_2023-08-24_204828.png

 

Any thing on that ?

Top
Tue, 2023-08-22 11:14
#2
Rafael Davila
User offline. Last seen 1 week 4 days ago. Offline
Joined: 1 Mar 2004
Posts: 5241
Groups: Spider Project Team

Any such formula that misses to consider CPM calendars (work hours, iregular work days and resource availability ...) will be wrong and misleading.

Top
Sat, 2023-08-19 14:20
#3
Rodel Marasigan
User offline. Last seen 2 days 10 hours ago. Offline
Joined: 25 Oct 2006
Posts: 1699
Groups: Petro-Chem / Oil & Gas

Hi Jithin,

the simple formula is = (Date - Start + 1) / (Finish - Start + 1) 
Say Column A= Start, Column B = Finish, Column C = Date ... and so on, then Row 1 & Row 2 

=(C$1-$A$2+1)/($B$2-$A$2+1)

Note: Assumption - date does not have time entry that is why it needs +1. If time is included on the entry (ex: 8:00 AM to 5:00 PM) therefore + 1 is not required)

Top