Is it possible to put some kind of date criteria in a formula? I'm new to formulae by the way.
I'm trying to count the number of tasks that have 2 custom checkboxes ticked and summarise the sum of them by the person they were assigned to. I'd like to show a summarised view over the last month and the last year.
Reports would be more ideal for this, but there are issues there as well so I'm looking for a solution with saved searches.
Currently this formula works in seeing if either checkbox is ticked and returns 1.
case when {custevent5}='T' or {custevent17}='T' then 1 end
Now I'd like to add a date qualifier within the formula so add some logic like:
case when date_task_complete = 'this year' then case when {custevent5}='T' or {custevent17}='T' then 1 end end
ie like a nested if statement
The outcome I'm after is:
col 1 list of people
col 2 sum of tasks for each person where either checkbox is ticked, for the last month
col 3 same as col 2, but for this year
Does that make sense?
So I'm interested to see if dates can be specified in a formula as that would then allow a similar consolidation as reports give.