Wednesday, December 12, 2012

yogi_Sort The Range Of Week Date Composites In Column C in Ascending Order


                                          Google Spreadsheet   Post  #924
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Dec 12, 2012
user awyatt said:(http://productforums.google.com/forum/#!mydiscussions/docs/YU2mY6eQe3A)
recognizing date ranges 
i have a spreadsheet that logs various issues by date, then automatically counts each type by week and by month. i have it set up so that the week and month titles automatically generate, so that when i put in a new entry for a given week the heading is automatically created. example:

       Input page                                      Tabulation Page
      A             B                              A                        B             C
    Date        Issue                        Week                  Issue 1    Issue 2
12/10/2012  Issue 1           12/16/2012-12/22/2012        0            1
12/10/2012  Issue 1            12/9/2012-12/15/2012         2            1
12/11/2012  Issue 2
12/17/2012  Issue 2


the problem comes where gdocs doesn't recognize "12/9/2012-12/15/2012" as a date range. the first cell (which says 12/16/2012-12/22/2012) contains a formula (thanks to someone on this forum!) like =SORT(ArrayFormula(filter(unique(if('Input page'!$A$2:$A="","",'Input page'!$A$2:$A-weekday('Input page'!$A$2:$A)+1&"-"&'Input page'!$A$2:$A+(7-weekday('Input page'!$A$2:$A)))),unique(if('Input page'!$A$2:$A="","",'Input page'!$A$2:$A-weekday('Input page'!$A$2:$A)+1&"-"&'Input page'!$A$2:$A+(7-weekday('Input page'!$A$2:$A))))<>""))). this sort recognizes "9" as being greater than "1", instead of seeing that it is less than "16", thus throwing off the sort

is there a way to get gdocs to recognize this date range? if not, how might one work around this? the monthly side works until a changeover in year, then of course january takes over!
--------------------------------------------------------------------------------------------------------
following is a solution to the problem