Add condition to column formula based on past date

celtics345

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

Kelly Moore

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

Kelly Moore

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

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.