Hey guys, I am using this formula to select o...

(Hyman van Zyl) #1

Hey guys, I am using this formula to select only tasks which were completed after a previous report up to the date of the current report. It is used in the report table.

=SELECT(Tasks[EntryID],AND([Project]=[_THISROW].[Project],[Done]=”Done”,[Date Done]>[Latest Previous Date],[Date Done]<=[Date]))

I am using a changetimestamp to register when someone selects task as done - [Date Done]. So I also made the report date [Date] and [Latest Previous Date] a DateTime column.

The problem is that it doesnt select the done or completed tasks very accurately.

I see that google sheets show the datetime without pm/am and appsheet with.

Could this be the problem? I cant find a way to make it similar. The locale is the same - United Kingdom. Any advice? Thanks lots Hyman

(Reza Raoofi) #2

The AM/PM thing in Google Sheet is just a matter of formatting; you can just format the whole column in Google Sheet to show AM/PM.

As for the formula, could you explain what you mean that it doesn’t select “very accurately”? Perhaps a sample of the actual data in those rows that don’t fit in the range as they should?

(Hyman van Zyl) #3

@RezaRaoofi

Hey Reza, So your question made me dig into the problem again and I found by changing the formula from this

=SELECT(Tasks[EntryID],AND([Project]=[_THISROW].[Project],[Done]=”Done”,[Date Done]>[Latest Previous Date],[Date Done]<=[Date]))

To this

=SELECT(Tasks[EntryID],AND([Project]=[_THISROW].[Project],[Done]=”Done”,[Date Done]>[Latest Previous Date],[Date Done]<=[THISROW].[Date]))

solved the problem. Thanks lots Hyman

(Reza Raoofi) #4

Yea, now that I am comparing it does make sense to have that [_THISROW].[Date] in there! :wink: