Google Spreadsheet Calculating Week Number

Question:

Hi 

Please help me with a formula if you can, i will be thankful to you.

To refer the sheet click this link:

'Column A' has date's and 'Column B' has ReasonI want to capture count of each reason between a week, week starts on Mon and ends Sunday. Example Mon, 17 Jun'2013 - Sun, 23 Jun'2013.

I want formula to be used in Sheet 2 but data remains in Sheet 1

Let me know if you have any queries.

Solution:

Put the following formula in any Cell in Sheet2:

=query(arrayformula(if('Sheet1'!B3:B="";"";if({1,1,0};'Sheet1'!A3:B;left(text('Sheet1'!A3:A;"'Week' w yy");7))));"select Col3,Col2,count(Col1) where Col3<>'' group by Col3,Col2 label Col3 '',Col2 '',count(Col1) ''")

Have a look at the screenshot of Sheet2 below:

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 and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

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.

iGoogleDrive