Hi, What must be formula for, two different d...

(Etwo Cargo) #1

Hi, What must be formula for, two different date column in different sheets.

HOUR(sheet1([date1])-(sheet2([date2])/24 is it true? Or what need to be?

(Levent KULAÇOĞLU) #2

@Etwo_Cargo are you asking this as a Spreadsheet formula or as an AppSheet expression?

(Etwo Cargo) #3

@Levent_KULACOGLU Dear Levent, The second one. AppSheet expression.

(Levent KULAÇOĞLU) #4

HOUR( ANY(SELECT(Table1[Date1],{Yes/No})) - ANY(SELECT(Table2[Date2],{Yes/No})) )/24

(Etwo Cargo) #5

@Levent_KULACOGLU says SELECT has invalid inputs.

(Levent KULAÇOĞLU) #6

@Etwo_Cargo {Yes/No} part in my example is a conditional expression that will evaluate to true to filter out the data gathered by SELECT. Like: ANY(SELECT(Table1[Date1], [Date1]>=TODAY ()))

(Steven Coile) #7

HOUR(LOOKUP(KeyVal1, “Sheet1”, “Sheet1KeyCol”, “Sheet1DateCol”) - LOOKUP(KeyVal2, “Sheet2”, “Sheet2KeyCol”, “Sheet2DateCol”)) / 24

LOOKUP(…) gets a single value from a specific column of a specific row in a table. Similar to @Levent_KULACOGLU’s ANY(SELECT(…)) expressions.

KeyVal1 and KeyVal2 represent the KEY column values in Sheet1 and Sheet2, respectfully, that uniquely identify the rows that contain the dates you want to compare.

“Sheet1” and “Sheet2” are the names of the two AppSheet tables.

“Sheet1KeyCol” is the name of the KEY column in Sheet1 where [KeyVal1] will be found. Likewise for “Sheet2KeyCol”.

“Sheet1DateCol” is the name of the column in Sheet1 that contains the date you want to use. Likewise for “Sheet2DateCol”.

The expression above is equivalent to:

HOUR(ANY(SELECT(Sheet1[Sheet1DateCol], ([Sheet1KeyCol] = KeyVal1)) - ANY(SELECT(Sheet2[Sheet2DateCol], ([Sheet2KeyCol] = KeyVal2))) / 24