Comparing Dates in Google Sheets with different format-CodePudding

I tried everything and can't make this work.

File 1 has a date and transactions. File 2 has date and other exported date from a software, so Column A is a date that is not formatted.

Basically I want to get the number of transactions per day on file 1, when in file 2 we have Column B with "google / cpc" and Column C contains "search".

The problem here is that I can't make the dates from File 1 to File 2 to compare to give me the transactions. It never compares.

File 1 https://docs.google.com/spreadsheets/d/1Xvoo2Rob3kI4duPpmCTfhMLPlvdzIJY9ZQBV7CHccoc/edit?usp=sharing

File 2 https://docs.google.com/spreadsheets/d/10Enq805we6_XcTkytwfj6ON1ZnITnAGUwLVoaGzXeco/edit?usp=sharing

I tried to make the date from file 2 like the date from file 1 using concatenate and LEFT and RIGHT formulas, but they look similar to the eye, but google sheets can't compare. I tried to also change the format to date and play with it, but still can't get them to compare the dates.

CodePudding user response：

Using your current formula,try changing your values with text and back to number with value:

=INDEX(IFNA(VLOOKUP(VALUE(TEXT(A3:A33,"yyyymmdd")), QUERY(IMPORTRANGE("10Enq805we6_XcTkytwfj6ON1ZnITnAGUwLVoaGzXeco", "Sheet2!A:N"), 
 "select Col1,sum(Col6) where Col2 = 'google / cpc' group by Col1"), 2, 0)))

CodePudding user response：

you can try this out:

=MAKEARRAY(31,1,LAMBDA(r,c,INDEX(LAMBDA(z,SUM(IFNA(FILTER(INDEX(z,,6),INDEX(DATE(LEFT(INDEX(z,,1),4),MID(INDEX(z,,1),5,2),RIGHT(INDEX(z,,1),2)))=INDEX(A3:A33,r),INDEX(z,,2)="google / cpc",REGEXMATCH(INDEX(z,,3),"(?i)search")))))(importrange("10Enq805we6_XcTkytwfj6ON1ZnITnAGUwLVoaGzXeco","Sheet2!A:F")))))