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
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?
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
Yea, now that I am comparing it does make sense to have that [_THISROW].[Date] in there!
User | Count |
---|---|
40 | |
34 | |
29 | |
23 | |
17 |