Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a column in Excel with 3,652 values. How do I split this column date-wise so that I have ten columns, one for each year, starting at a specified date?

From comments:
I am working with daily climatological data of about 20 years. This makes a long time-series of 2 columns, first containing date and second containing values. I have to calculate annual and monthly averages for which I have to use copy and paste function to arrange year-wise or month-wise, which takes a lot of time and effort. But I have read about Macros and Array functions but don't know how to write them.

share|improve this question
    
Welcome to SuperUser! This is an interesting question, so you may wonder why you've received down votes. Typically users here want to see what you've tried to solve your problem, just so it's clear that you aren't asking "Hey, will someone do my work for me?" If you add a little detail showing what you've tried or what ideas you have but don't know how to implement, you're more likely to get upvotes and answers. –  Excellll Jul 19 '13 at 14:03
    
@Excellll Would you mind if I save me your text for further questions? I like your explanation :) –  nixda Jul 19 '13 at 15:05
    
@nixda Sure, go ahead. –  Excellll Jul 19 '13 at 15:33
    
Pradeep, when you say ten columns, "starting at a specific date", do you mean a date other than Jan 1 of the year? –  chuff Jul 19 '13 at 16:44

3 Answers 3

You can do this with an array formula on the worksheet.

Go ahead and create your column headers manually (label as the year, e.g. 2013, because this will be used in the formula).

In the first row below the first header, enter the following formula as an array formula (paste the formula into the formula bar and press Ctrl+Shift+Enter), where A2:A3653 is your big column of dates and B1 is the header for the first year's column:

=IFERROR(TEXT(SMALL(IF(YEAR($A$2:$A$3653)=B$1,$A$2:$A$3653,""),ROW()-1),"m/d/yyyy"),"")

In this example, the formula is entered in row 2. If your output columns start on a different row, you will need to adjust the ROW() - 1 part of the formula so that ROW() - x is equal to 1 for the first date (e.g., if your headers are on row 3 and the first row of dates is in row 4, you will want to change this part of the formula to ROW()-3.

This formula can be filled over through all 10 columns. Finally fill down as far as you need (probably 400-500 rows, depending on the distribution of the dates). Array formulas do a lot of computational work, so this will probably take a few seconds to calculate.

Once your data is split, you may want to copy all these cells and paste special the values back into the same range. This will remove the formulas and improve the performance of your worksheet.

share|improve this answer
    
@ Excell: I am working with daily climatological data of about 20 years. this makes a long time-series of 2 columns, first containing date and second containing values. I got to calculate annual and monthly averages for which I have to use copy and paste function to arrange year-wise or month-wise, which takes a lot of time and effort. But I have read about Macros and Array functions but don,t know how to write them. And I was here. –  Pradeep Jul 21 '13 at 12:29

REVISED based upon further information provided by OP

As I understand it, you want to transform a column of values into a table with each column representing a year's worth of values starting at a specified date. In developing my answer, I have made three assumptions to deal with possibilities that your question raises but does not address:

  1. The values in the original column are already associated with specific dates, i.e., the first value is tied to a specific date, say, Jan. 1, 2013, the second value to Jan. 2, etc.

  2. Those values may be numerical data or they may be consecutive dates.

  3. The specified date may be any date in the year.

The following formula will produce the output you want. It should be entered into the initial cell of the output table copied across for a total of ten columns for the ten years of values, and down through the 366 rows of values for each year, including a row for the extra day in leap years. (The full setup of the worksheet is shown in the pictures below.)

=IFERROR
        (
         INDEX
              (
               Data1,
               IF
                 (
                  AND
                     (
                      DATE(YEAR(E$2)+1,MONTH(E$2),DAY(E$2))-E$2=365,
                      ROW(E3)-ROWS(E$2)-1=366
                     ),
                  "-",
                  E$2-$C$2+COUNTA(E$2:E2)
                 ),
               1
              ),
         "-"
        )

This expression is essentially an INDEX formula, elaborated to deal with what to show in the 366th row of the output table when the year is not a leap year. Data1 is the named range of data values; the IF statement returns the row index, except in non-leap years, when it replaces with a dash (-) the calculated row index for the 366th row of the output table.

The output table proper begins in cell E2. If your table is place differently in the worksheet, you will need to adjust the anchored references E$2, $E$2 and $C$2 in the formula to reflect where the first cell in the header row of the table starts and where the date for the first value is.

The picture below shows the values (in this case, just a set of dates beginning with 1/1/2013), the two other inputs needed, and the resulting output table with a starting date of 1/4/2013.

The middle section of the picture shows the bottom of the output table, which correctly displays one extra value for the 2016 leap year.

The bottom section shows that the 3652 data values are padded with the dash character, which is used to indicate when no more values remain in the tenth year, due to a start date after the beginning of the year.

Data, input cells, and output table using date values

The image below shows the same worksheet using (random) values other than dates as input. The only change in the output formulas is the use of the named value range Data2 instead of Data1.

Data, input cells, and output table using numeric values

share|improve this answer
    
Thanks, but is there any "Calender Intelligent" Macro so that I may not have problems with leap years or while trying to split month wise... –  Pradeep Jul 19 '13 at 13:05

By the extra information in your question, I would add 3 extra columns, and make a pivot table

I am assuming the date is in column A, and the data in column B.
e.g.

Date       Rainfall
1/1/1994   0.04
1/2/1994   0.02
...
the extra columns would make the table look like this:

Date       Rainfall  Month       Year       YYYY-MM
1/1/1994   0.04      =MONTH(A2)  =YEAR(A2)  =TEXT(A2,"YYYY-MM")
1/2/1994   0.02      =MONTH(A3)  =YEAR(A3)  =TEXT(A3,"YYYY-MM")
...

Then, I could make up a pivot chart that has things like a month/year comparison (months along one axis, and years along another), and also allows me to do yearly and monthly totals by putting the appropriate columns along the axis

share|improve this answer
    
Thank you @Sean. –  Pradeep Jul 24 '13 at 10:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.