2

I'm trying to query a column based on the current day.

Examples:

If today is 1st day of the month I need to:

=QUERY(table,"SELECT A WHERE B CONTAINS 'this text'")

If today is 12th day of the month I need to:

=QUERY(table,"SELECT A WHERE M CONTAINS 'this text'")

If today is 30th day of the month I need to

=QUERY(table,"SELECT A WHERE AE CONTAINS 'this text'")

How can I do this?

I was thinking about adding +1 to the current day, converting it to its corresponding letter and using it in the query. But I have no idea how to actually do that.

0

You can use the following QUERY formulas:

If today is 1st day of the month:

=IF(DAY(EOMONTH(TODAY(),-1)+1)=1,QUERY(table,"SELECT A WHERE B CONTAINS 'this text'")," ")

If today is 12th day of the month:

=IF(DAY(EOMONTH(TODAY(),-1)+12)=12,QUERY(table,"SELECT A WHERE M CONTAINS 'this text'")," ")

If today is 30th day of the month:

=IF(DAY(EOMONTH(TODAY(),-1)+30)=30,QUERY(table,"SELECT A WHERE AE CONTAINS 'this text'")," ")

How the formula works:

  • TODAY()= today's date.
  • EOMONTH(TODAY(),-1)= last day of last month.
  • DAY(EOMONTH(TODAY(),-1)+1)= 1st day of this month (last day of last month + 1 day).

So, the 12th day of this month is: DAY(EOMONTH(TODAY(),-1)+12)


PRO TIPS:

  1. The last day of this month (either 28th or 29th or 30th or 31st) is always =DAY(EOMONTH(TODAY(),0)
  2. If you place the date of the IF function in a cell outside the query In eg. cell A1 you can have numbers like 1 or 12 or 25 etc and change your query to:

=IF(DAY(EOMONTH(TODAY(),-1)+A1)=A1,QUERY(table,"SELECT A WHERE M CONTAINS 'this text'")," ")

Functions used:

| improve this answer | |
  • Welcome. Please remember that as per site guidelines when an answer addresses your question, you should accept it and even upvote it so others can benefit as well. – marikamitsos Apr 8 at 21:03
  • I need 1 cell to display data for today. If it's the first day I need it to display data from A where B contains text. If it's the second day I need it to display data from A where C contains text, and so on for the whole month. I need that cell to detect what's day today and display data for that day. – Eldin Turkic Apr 8 at 21:22
  • Please check the updated answer for ore info and tips. – marikamitsos Apr 8 at 21:41
  • If this answer is not what you are looking for, please read how to and share a test sheet so as you can be easier helped. – marikamitsos Apr 8 at 21:49
  • Code that you gave me gives me #VALUE! But ere is the example sheet of what I'm trying to get docs.google.com/spreadsheets/d/… Data for each day are stored in Sheet1 And Sheet2 needs to show selected data for today and tomorow Because today is 9th day, that corresponds to column "E" from Sheet1, and I need to show data that contains "x", so I used =QUERY(table,"SELECT A WHERE E CONTAINS 'x'") – Eldin Turkic Apr 8 at 22:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.