date range formulas

blair myles

Can anyone help me please with the below formula. it all works fine except the date section at the end. what i am trying to achieve is a condition that captures entries that fall between 2 dates in the past. eg last 60 days and last 30 days

 

=SUMIFS([Actual Hours]43:[Actual Hours]602, Status43:Status602, "Complete", Type43:Type602, "Arch", Finish43:Finish602, =([MOS Date]1 < >[MOS Date]2))

Tim Meeks

Blair,

 

Is the above formula giving you an error message?  You may have an extra "{" bracket right after the =sign. Have you tried to replace it with a regular parenthesis?

 

Also, I noticed in your date field, you are checking the same column but checking 2 fields directly above and below each other?  Does the forumula move down to the next cells (3 and 4) as it moves down the sheet?

 

I'm not the most advanced SUMIFS formula expert, but here is one thought...I try to simplify my formulas instead of making them do too much in one command.  

Just a suggestion.  What if you add another column called "MOS Date Check" and in that column have a formula that only determines if =([MOS Date]1 < >[MOS Date]2))... then if the value is TRUE set the result to 1 ELSE "".

 

As example:  =IF([MOS Date]1 <> [MOS Date]2, 1, "")

Then in your original formula check for the value = 1 in the MOS Date Check cell.

 

So your formula might be: 

=SUMIFS([Actual Hours]43:[Actual Hours]602, Status43:Status602, "Complete", Type43:Type602, "Arch", Finish43:Finish602, [MOS Date Check]1  = 1)

 

Other than, I would need to see a screen shot of your actual sheet and values ti assist further.

 

Hope I helped more than not. 

Tim

blair myles

perfect thanks. that worked a treat:)

Tim Meeks

Thanks for the feedback. Always glad when something helps. So which suggestion solved the issue?

 

thanks,

Tim