CODEWITHSUNDEEP
google-sheets
Cody Cook
Cody Cook

Reputation: 35

Show only the current Week

I am working with Google sheets, and the Form responses that get poured into it. I am wondering if there is a way to show the CURRENT work week? What I mean by this is a sheet that shows this Friday:

MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY 124 123 193 -- 234 344

But then the next Monday:

MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY 088 -- -- -- --

Is this possible, or is it to much for google sheets?

Upvotes: 2

Views: 4854

Answers (1)

user3717023
user3717023

Reputation:

It's certainly possible. To begin with, I would put dates under Monday, Tuesday, etc: this simplifies subsequent computations, and is also helpful when reading the data. This would go under "Monday": it returns the Monday of the current workweek:

=today()-weekday(today())+2

Here +2 compensates for subtraction when the date is Monday (which is numbered 2). The other days of the week can be obtained by adding 1 to Monday (=A2+1), or directly by using =today()-weekday(today())+3 and so on.

Then you can pull data from the form sheet using functions such as query, filter, vlookup, ... For example, this formula returns the 3rd column from Form Responses 1 where the date is nearest to the content of A2 (current Monday) 

=vlookup(A2, 'Form Responses 1'!A:A, 3)

A possible issue here is that Form Responses record both date and time, so the "nearest" record may be from 11:50pm of previous day. One way to solve this is to use filter:

=filter('Form Responses 1'!C:C, floor('Form Responses 1'!A:A) = A2)

This returns all C column entries from the form where the date in the A column matches the content of A2.

Upvotes: 2

Related Questions