Formula problems around dates

[WardyTHFC]

Hi Everyone

I am creating a forecasting tool for the amount of hours required of staff vs Staff availability.

The Input that the user will use is a list of Projects and the dates of different phases of the project.

The output will be a projection of the amount of hours required per month over a 3 year period. The first step involves this formula:

=SUMIFS('Resource Estimates'!$E:$E,'Resource Estimates'!$B:$B,$E4,'Resource Estimates'!$D:$D,T$1,'Resource Estimates'!$C:$C,'Gate Date'!T$2,'Resource Estimates'!$A:$A,'Gate Date'!$M$3,N:N,$M$2)

This formula has a sum range of average required hours per month

The formula has the Criteria for Complexity of project, role, phase.

The area that I cannot get to work is the Criteria at the end of the formula where it is looking for the year of the phase to align with the year in field M3 - A picture is attached to assist.

I have tried multiple things, I am aware that dates are allocated a serial no. and I have tried to align these but that does not work for me.

Can anyone help?

1.jpg

[Norie]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic