Friday, October 5, 2012

Google Spreadsheet formula to display dates which are all Tuesdays

Question:

I want to write a formula in Google Spreadsheet that look into the range between two dates and will display all dates which are all "Tuesday".

Is there a formula to do this?


Solution:

Suppose you have dates in Cell B1 and Cell B2.

Then put the following formula in Cell C1:
=FILTER(ARRAYFORMULA(B1-1+ROW(INDIRECT("B1:B" & (B2-B1+1))));ARRAYFORMULA(CHOOSE(weekday(ARRAYFORMULA(B1-1+ROW(INDIRECT("B1:B" & (B2-B1+1)))));"Sunday";"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday"))="Tuesday")

The above formula will display you the dates in Column C which all will be Tuesdays.

Have a look at the following screenshot:






I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private or confidential projects:

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,
Kishan,


3 comments:

  1. Very nice ! What if you want the formula to show Tuesdays AND Wednesdays ? I need something like this, so all help is appreciated.

    ReplyDelete
  2. Better yet: would it be possible to have a range with values after the =-part of the Choose-formule ? Main thing is to filter with multiple conditions (in this case: more then just one single day)...

    ReplyDelete
  3. Got it: =filter(ARRAYFORMULA(B1-1+ROW(INDIRECT("B1:B" & (B2-B1+1)))); (match( weekday(ARRAYFORMULA(B1-1+ROW(INDIRECT("B1:B" & (B2-B1+1))));2) ; Lesdagen; 0)))

    P.S.: "Lesdagen" is a named range: B2:B6. In this range I inserted the weekdays (in numbers !) I needed.

    Couldn't have done it, without your help, though.... THANKS !!!

    ReplyDelete