fill values in a formula in one cell with value from another cell

Chood

I have a bunch of formulas that pull figures from another sheet based on a start and stop date. They work just great.

I am trying to keep from having to change the date over and over in all of the formulas for each week of each month by using "Helper" Cells. You put the desired date range in for each week one time and the formulas below are populated with these date values.

Attached is a screen shot of what I am trying to do. Anyone have an idea on the proper syntax for this?

Chood

This is a snippet of the formula working, but referring entirely to the other sheet.

Paul Newcome

Is your [Week] column set as a date type column?

Chood

It is just set to Text/Number

Chood

By the way, I did have the start and end dates backwards in the above references, but correcting that did not help.

Paul Newcome

In that case you would need to use something along the lines of this in place of the existing DATE functions.

DATE(VALUE(LEFT(Week6, 4)), VALUE(MID(Week6, FIND(" ", Week6) + 1, (FIND(",", Week6, FIND(" ", Week6)) - 1) - (FIND(" ", Week6) + 1)), VALUE(RIGHT(Week6, LEN(Week6) - FIND(" ", Week6, FIND(" ", Week6) + 1))))

Chood

My brain cramped and face started sweating profusely just reading that. LOL

I may just have to change the date references in the fields manually. That is way beyond my ability to decipher.

😲

Paul Newcome

In your existing formula... Anywhere you see DATE(.......), replace it with the above. You may need to change the number in each reference to "Week6" to reflect the correct row, but that should be about it other than some copy/paste.

Chood

Here is what I tried. I pasted the above formula anywhere DATE() was and changed Week6 to the row number corresponding with the start date and end date as as shown below. Week row 95 is start date and Week row 96 is end date. I am still getting something wrong.

=SUMIFS({Jobs Revenue}, {Jobs End Date}, @cell >= DATE(VALUE(LEFT(Week95, 4)), VALUE(MID(Week95, FIND(" ", Week95) + 1, (FIND(",", Week95, FIND(" ", Week95)) - 1) - (FIND(" ", Week95) + 1)), VALUE(RIGHT(Week95, LEN(Week95) - FIND(" ", Week95, FIND(" ", Week95) + 1)))), {Jobs End Date}, @cell <= DATE(VALUE(LEFT(Week96, 4)), VALUE(MID(Week96, FIND(" ", Week96) + 1, (FIND(",", Week96, FIND(" ", Week96)) - 1) - (FIND(" ", Week96) + 1)), VALUE(RIGHT(Week96, LEN(Week96) - FIND(" ", Week96, FIND(" ", Week96) + 1)))))))

I apologize for seeming so dense on this. I promise, I am actually trying really hard to understand and thank you for your efforts.

Paul Newcome

Are you getting an error or an unexpected number? Are you able to paste the SUMIFS from your original screenshot, so I can drop things in myself to see if there are any syntax issues?