Can someone help me?
I need to take a clock in entry in table โTime Entriesโ, and compare that to 2 Columns in another table and return a third column.
Basically, in the table โTime Entriesโ thereโs a time punch.
I need to compare that to a column called โPay Period Start Date / Timeโ and another column called โPay Period End Date / Timeโ and see where the original time punch falls between those 2 dates, and then return the column called โProjected Pay Dateโ
I can easily do this with a Filter( in the google sheet, but how can I do this in appsheet?
The google sheet formula looks like this:
=filter(Reference!G:G,J51>=Reference!E:E,J51<=Reference!F:F)
I donโt know if I need to be using LOOKUP or FILTER or what in appsheet. Any help would be most appreciated.
Side note, this would be much easier if someone has a formula to take a โClock in Date / Timeโ entry and auto calc the next coming Friday
Thank in advance
@Joe_Seiler
Do you have a common reference between these 2 tables?
No, other than the row #
@Joe_Seiler
So how do you intent to find the matching value in another table? You should have a reference for that.
I want to see if appsheet can take the value in the first table named [Clock In Date / Time], and basically query the second table to see which value in [Pay Period Start Date / Time] itโs greater than, and which value in [Pay Period End Date / Time] itโs less than, and then return the value in the same row as from the column [Projected Pay Date].
Just like a =filter( formula would work in a google sheet
Every day of the year isnโt listed in the reference table. Just a starting and ending day of the period. However, I suppose I could list every day of the year in the reference table, along with itโs associated pay date. I could make that column a key for that table
@Joe_Seiler
You can try with this expression:
LOOKUP(
FILTER(
"ReferenceTableName",
AND(
([Pay Period Start Date / Time] >= [_THISROW].[Clock-in]),
([Pay Period End Date / Time] <= [_THISROW].[Clock-in])
)
),
"ReferenceTableName",
"KeyColumnName",
"Projected Pay Date"
)
User | Count |
---|---|
37 | |
31 | |
28 | |
22 | |
18 |