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 ...