# Dashboard based on Todays Date - From a table

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:

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

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

Thanks for any help!

• 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

## EDIT

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.

WHY?
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

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:

=TODAY()

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)&"")

Functions used:

• Your solution is much better than mine. Thanks!
– Tizz
Aug 14, 2020 at 0:09
• 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