To calculate % Completed for that month.

Vanmi

New Member
Joined
Apr 11, 2023
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
  2. Web
Good Morning All, I'm trying to write an Excel formula, but not getting the intended results, If any Excel advanced users in our team, help me with this formula. Here is the scenario, I'm trying to calculate the duration achieved for each month. Take a look at the image attached, for the task name "Build", have a duration of 9 days, both start and finish date are in the same month,60% completed. so the duration achieved for March 2023 month is calculated as duration multiplied by % Complete. and for the next task "Supervision" have a duration of 140 days starting on March 05 23 to 04 June 23, now how to calculate the Duration Achieved for the month of March 2023? as the finish date falls under a different month? I'm looking for any formula or function suggestions in Excel.
 

Attachments

  • AAAAA.PNG
    AAAAA.PNG
    8.3 KB · Views: 8

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Check the following :

Book6
ABCDEF
1NameDurationStartFinish% completeDuration achieved for the month
2Build920/03/202330/03/202360%5
3Supervision14005/03/202304/07/202310%14
Sheet1
Cell Formulas
RangeFormula
F2:F3F2=ROUND(B2*E2,0)
 
Upvote 0
Hi Sanjeev,
Thanks for your response, Above formula, works well for the first month of the supervision task. suppose I'm calculating for the month of April 2023, how to calculate the duration achieved for that month? As both the start and finish dates fall in different months? If I used the above formula, it's showing value for the overall duration achieved for the task, not for each month duration achieved.kindly advise.
 
Upvote 0
Hello Vanmi,
This depends in which column will you record the monthly progress. So, if you want to show the duration achieved in April and you will show 30% instead of 10%, how will the system know that the progress in previous month was 10% and then progress in current month is 20%.

To achieve the above you can adopt your format as shown below :

Book7
ABCDEFGHIJ
1NameDurationStartFinish% completeDuration achieved for the month
2Mar-23Apr-23May-23Jun-23Jul-23
3Build920/03/202330/03/202360%10%1
4Supervision14005/03/202304/07/202310%30%42
Sheet1
Cell Formulas
RangeFormula
E2E2=TEXT(C4,"mmm-yy")
F2:I2F2=TEXT(EOMONTH($C4,COLUMNS($F$1:F1)),"mmm-yy")
J3:J4J3=ROUND(B3*LOOKUP(2,1/(E3:I3<>""),E3:I3),0)
 
Upvote 0
Solution
For future reference.

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: To calculate % Completed for that month.
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top