I have a list of students with responsibilities and I would like to create a dashboard in Google Sheets with information about the current month.

As an example:

enter image description here

then in September 2020, I want the dashboard to show:

enter image description here

... and also update automatically to Jose and Gina once we are in Oct 2020

Here is the demo sheet: https://docs.google.com/spreadsheets/d/113ZAUJgbzgtjEGai9LkM22h-AEyaPymxUHMnu-RLYEw/edit?usp=sharing

Thanks for any help!

  • 1
    Welcome. Please read how to and share a test sheet so as you can be easier helped. Aug 13, 2020 at 18:24
  • Thank you ... Added :)
    – Tizz
    Aug 13, 2020 at 23:33

2 Answers 2



As very correctly noticed in the comments, there is a hasty, common, still unforgivable mistake in my original formula.

The correct formula is

=QUERY(Responsibilities!A2:C,"select B, C where month(A)="& month(A2)-1&"")

Please do notice that in order to get the current month one needs to subtract 1 month.

Because in a query formula, when expressing months in numbers January corresponds to 0
So the order of the months is:

  January       0  
  February      1  
  March         2  
  April         3  
  May           4  
  June          5  
  July          6  
  August        7  
  September     8  
  October       9  
  November     10  
  December     11  

Original answer:

You would need 2 formulas in your dashboard sheet

Formula 1
You would first need to put today's date in a cell and refer to that cell to get the current month. That formula is:


Formula 2
You can now use the following formula to get your results

=QUERY(Responsibilities!A2:C,"select B, C where month(A)="& month(A2)&"")

enter image description here

Functions used:

  • Your solution is much better than mine. Thanks!
    – Tizz
    Aug 14, 2020 at 0:09
  • 1
    This was giving me the month ahead of today(), so I added an EDATE -1 and now its working properly for me ... =QUERY(Responsibilities!A2:C,"select B, C where month(A)="& month(EDATE(A2,-1))&"")
    – Tizz
    Aug 14, 2020 at 0:19
  • No need to complicate things using the EDATE function. Just use month(A)="& month(A2)-1&". Aug 14, 2020 at 0:25

I found a way to do it myself. Only took 4 hours :(

=Index(Responsibilities!$B$2:$B$7, MATCH(DATEVALUE(CONCATENATE(MONTH(today()),"/1/",Year(today()))),Responsibilities!A2:A7,0),1)
  • Great work. You can try my solution as well. I believe it is simpler. Aug 14, 2020 at 0:10
  • Yours will auto fill with one formula, I will have more than these 2 columns so its nice, but if I dont want to have cells next to eachother, I will have to break up your solution as well.
    – Tizz
    Aug 14, 2020 at 0:14
  • Since it is a query you can rearrange the columns and even omit some according to your needs. Aug 14, 2020 at 0:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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