Is there a way to find the time between 2 dates after subtracting in excel?

user_17331335 0
Viewed 37

I am given this task to complete

"What is the average time for a project to go to 3-Offered per year for the years 2016, 2017, and 2018? Use the formula # Days = [Date Offered] - [Start Date]. Only projects where [Project Template] = "Pre-Approval" will have an offered date, no need to include Fast Track projects in the data."

my current function I am trying to use is =AVERAGE(IF((B:B="Pre-Approval")*(C:C="3-Offered")*(YEAR(D:D)=2016),E:E-D:D)) but I am getting the #VALUE! and I'm not sure why.

I will link a screenshot of some of the data used for this task.

[Column D is the start date and Column E is the Offered Date]

DataSheet

excel-formula excel database
1 Answers

I think it is easier if you break down the problem into smaller pieces.

First, I would separate the years from the offered date. On a different column, e.g. Year, I would apply =YEAR() for every item in your Offered Date column.

Second, I would take another column, call it Diff, and apply =OfferedDate-StartDate.

Then, for each individual year I would apply =AVERAGEIFS(diff_column_range, year_column_range, "3-Offered", year_column, required_year)

I have an example for you here: https://1drv.ms/x/s!AhMbe-MPmuFCggjutWepn-QtMri4?e=CxpTCW

Let me know if this works for you.

Cheers!

user_122480840
Related