SUMIFS with date range formula question

Evan Summerville

Greetings,

I'm attempting to sum the number of project management hours for the project managers in my function for given points in time to generate a capacity forecast for 2024. I'm currently building a sheet to depict capacity every 2 weeks based on if a date falls between project start and end that will be built off a cross sheet referenced formula.

Currently the formula DOES NOT WORK and reads:

=SUMIFS({Weekly Hour Forecast}, {Assignee}, "first.last@mycompany.com", {Target Start}, "<=" & TODAY(), {Target End}, ">=" & TODAY())

Columns being referenced in a different sheet(PMO Portfolio):

1. Weekly Hour Forecast - estimated weekly hours per project for an individual
2. Assignee - the assigned project manager
3. Target Start - start date for the project
4. Target End - end date for the project

My intent is to create 27 columns (1 for project manager name, and 26 representing 2 week periods). So, in an ideal scenario the date component should be changed the following instead of using TODAY() to determine if hours need to be summed based on if a project is ongoing (date falls between target start and target end):

1. Jan 8, 2024
2. Jan 22, 2024
3. Feb 5, 2024
4. Feb 19, 2024

And so on through the end of the year.

As mentioned above, my cross sheet formula currently does not work, does anyone have suggestions as to how to fix it, and as a bonus swap in some of the dates I listed above in place of TODAY?

Thanks in advance!

Paul Newcome

Your argument syntax is off.

=SUMIFS({Weekly Hour Forecast}, {Assignee}, "first.last@mycompany.com", {Target Start}, @cell <= TODAY(), {Target End}, @cell >= TODAY())

To use hard coded dates, you would use the DATE function.

DATE(yyyy, mm, dd)

Paul Newcome

Your argument syntax is off.

=SUMIFS({Weekly Hour Forecast}, {Assignee}, "first.last@mycompany.com", {Target Start}, @cell <= TODAY(), {Target End}, @cell >= TODAY())

To use hard coded dates, you would use the DATE function.

DATE(yyyy, mm, dd)