Monday, April 23, 2012

yogi_Sum Up Data In Specified Column Between Two Dates And Present By Year And Month

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #513    Apr 23, 2012     www.energyefficientbuild.com.

user BobRoss said:
Formatting date number to keep trailing zero for October to not confuse with January 
Please consider:
=query(importrange(ʺ0Ao4BSCJLCiKUdEpvUXliSXQxZC1Wb2h6TlI2MFZ5amcʺʺData!A:Cʺ), 
ʺselect Col2, sum(Col3) where Col1 is not null and Col1 >= date 'ʺ & text(A13ʺyyyy-mm-ddʺ) & ʺ' and Col1 <= date 'ʺ & text(A14ʺyyyy-mm-ddʺ) & ʺ' group by Col2 pivot year(Col1) + (month(Col1) +1) * .01ʺ)
Note the pivot clause:
pivot year(Col1) + (month(Col1) +1) * .01ʺ)
Which can produce the groups:
2011.1 (October 2011)
2011.01 (January 2011)
Problem:
2011.1 may seem like January 2011 to some users.
What I'm trying to do:
Display 2011.1 as 2011.10 to avoid confusion.
Can you help?
-------------------------------------------------------------------
as of now, QUERY function in Google spreadsheet does not support Format option ... as a workaround
following is a solution to the problem

here is the data we are going to work with ...