Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

date range formulas

blair myles
edited 12/09/19 in Archived 2016 Posts

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
    Tim Meeks ✭✭✭✭✭✭



    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. Wink


  • blair myles

    perfect thanks. that worked a treat:)

  • Tim Meeks
    Tim Meeks ✭✭✭✭✭✭

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




This discussion has been closed.