Add condition to column formula based on past date

Options

Hello all,

I have this column formula that aims to tally hours worked based on certain parameters.

=ROUND(SUM(COLLECT([Actual Observing Hours]:[Actual Observing Hours], ObserverContact:ObserverContact, ObserverContact@row, Discipline:Discipline, Discipline@row)))

This formula works, however I want to make one addition. Currently it's tallying all numbers in a specified column [Actual Observing Hours]. I want it to tally only numbers that have occurred in the past, based on a preexisting date column [Event Date]. How would I add this exception into the above formula?

If it requires a helper checkbox column, I've already built an automation that checks a box in the [Past Date] column once a day has elapsed. We can either use the [Event Date] date column or the [Past Date] checkbox helper column, doesn't matter to me.

Thank You, Brian

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @celtics345

    Try this

    =ROUND(SUM(COLLECT([Actual Observing Hours]:[Actual Observing Hours], [Event Date]:[Event Date], ISDATE(@cell), [Event Date]:[Event Date], <TODAY(), ObserverContact:ObserverContact, ObserverContact@row, Discipline:Discipline, Discipline@row)))

    The ISDATE() will help prevent errors from any non-date values that you may have in your [Event Date] column.

    Will this work for you?

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @celtics345

    Try this

    =ROUND(SUM(COLLECT([Actual Observing Hours]:[Actual Observing Hours], [Event Date]:[Event Date], ISDATE(@cell), [Event Date]:[Event Date], <TODAY(), ObserverContact:ObserverContact, ObserverContact@row, Discipline:Discipline, Discipline@row)))

    The ISDATE() will help prevent errors from any non-date values that you may have in your [Event Date] column.

    Will this work for you?

    Kelly

  • celtics345
    Options

    That worked absolutely perfectly! Thank you so much. Always frustrating seeing how close I am to getting it, yet so far away haha. Was just missing the ISDATE(@cell) part. Thank you again Kelly.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!