DATE CALCULATION FORMULA QUERY

Question

I am trying to identify a completion rate (%) using a start and end date to identify the total actual number of days to complete some training compared to the expected days of training.
I think I need to use the dates from column H and subtract that from the dates in column CH then divide by 3 (expected days training) and create a percentage. I just cant get this formulae right!
Can anyone help me please?

Answer 1

Dear @Vivienne Cremona,

Thank you for your question. If you would like to calculate a completion rate (%) based on the actual number of days taken to complete training compared to the expected duration, you can do this in Power Query by following the steps below:

1. Ensure your data contains at least the following columns:
   • Start Date
   • End Date
   • Expected Days (for example, 3)
2. Select your data range and press Ctrl + T to convert it into an Excel Table.
3. Go to the Data tab and select From Table/Range to open Power Query Editor.
4. In Power Query, create a column to calculate the actual number of days:
   • Select Add Column > Custom Column
   • Name the column Actual Days
   • Use the following formula: Duration.Days([End Date] - [Start Date])
5. Create another column for the completion percentage:
   • Select Add Column > Custom Column
   • Name the column Completion %
   • Use the following formula: [Actual Days] / [Expected Days]
6. Select the Completion % column.
   • Go to Transform > Data Type > Percentage.
7. Once finished, select Home > Close & Load to return the results to Excel.

If you are unable to get the steps working or encounter any errors, please feel free to send your workbook to me via Private Message and I will be happy to review the file structure and help determine the correct formula for your specific data.

For your privacy and security, please send the following information via Private Message by clicking the "Private messages" button shown below:

Thank you for your cooperation and understanding.

I look forward to hearing from you.

---

If the answer is helpful, please click "Yes". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.