1

I have a pivot table arranged by day and year. Unfortunately, days come up as entries under 'year' headers. Is there a way to make it so that the year is included in the actual entries rather than just being a header? If I were to copy the values from this pivot table is there a way to append, say, 2014 to the cell and have it read as a date?

The table looks something like this:

Year 1     Count
02-August   1
05-August   1
09-August   4
Year 2
02-August   3
5
  • What does your data look like? How's the pivot table set up? Sep 24, 2014 at 17:02
  • @Ray Updated with a sketch of what the table looks like.
    – 114
    Sep 24, 2014 at 17:46
  • Are the dates (e.g. "02-August") formatted as dates or text?
    – Iszi
    Sep 24, 2014 at 20:03
  • @Iszi They are formatted as dates, or at least Excel claims they are when I move over them.
    – 114
    Sep 24, 2014 at 22:18
  • If they're formatted dates, the last bit of my solution might not work - it assumes they're string values. Let me know if it's broken and I'll look into what's needed to fix it. Or, if you manage to fix it yourself, please let me know what you did.
    – Iszi
    Sep 24, 2014 at 22:33

1 Answer 1

0

Given your example table, and assuming that the cell containing the "Year 1" header is A1, you can do this to get a column that lists "Year X" on each row.

Cell C2: =IF(LEFT(A1,4)="Year",A1,IF(LEFT(A2,4)="Year","",C1))

Breaking it down:

  • =IF(LEFT(A1,4)="Year", - The first IF statement will be TRUE when the first four characters in A1 are "Year".
  • A1, - When the IF statement is TRUE, C2's value will copy A1.
  • IF(LEFT(A2,4)="Year", - When the first IF statement is FALSE, the second IF statement will check to see if A2 starts with "Year".
  • "", - When the second IF statement is TRUE (and the first is FALSE), C2 will be blank.
  • C1)) - When both IF statements are FALSE, C2 will pull the value from C1.

For C2, the formula will return "Year 1". Copy the formula down the rest of the column, and the cell references will automatically adjust so that the "A1" references always point two to the left and one up from the current cell, and the "C1" reference always points to the cell immediately above the current cell. So, for your current list, column C will be added so the table looks like:

Year 1      Count Year
02-August   1     Year 1
05-August   1     Year 1
09-August   4     Year 1
Year 2
02-August   3     Year 2

To translate the years into regular year values, you'll need either a lookup table that defines which year ID matches to which calendar year, or you at least need to know what "Year 1" is. How you do it without a lookup table may be dependent on your formatting in column A, so I'll just

Using a lookup table in columns F:G (column F has "Year X", replacing X with actual numbers, column G has the corresponding four-digit years), here's how to put the year in D2: =IF(C2="","",VLOOKUP(C2,F:G,2,FALSE))

  • =IF(C2="","", - If C2 is blank, leave D2 blank. Otherwise, the VLOOKUP would error on blanks.
  • VLOOKUP(C2,F:G,2,FALSE)) - When C2 isn't blank, look for the value of C2 in the left-most column of F:G, and return the corresponding value from the second column. Only use exact matching.

Without the lookup table, as long as the years are consecutively numbered, you could do this in D2: =IF(C2="","",RIGHT(A2,LEN(A2)-5)+2012-1)

  • =IF(C2="","", - If C2 is blank, make D2 blank. Otherwise...
  • RIGHT(A2,LEN(A2)-5) - Take all but the first 5 characters from A2. (This should be just the "X" part of "Year X".)
  • +2012 - Add 2012 (replace 2012 with the actual year you want for Year 1).
  • -1) - Subtract 1 (since "Year X" starts from 1. Otherwise, all your year values will be one over, or you'll need to adjust "2012" (your "Year 1") to be "2011" (the year prior to "Year 1").

If you just want to tack the year onto the end of the date, and don't care about having dates formatted as actual dates, you could then do this:

Cell E2: =IF(D2="","",CONCATENATE(A2," ",D2))

  • IF(D2="","", - If D2 is blank, leave E2 blank. Otherwise you'll have cells with "Year X" type values in them now and then.
  • CONCATENATE(A2," ",D2)) - If D2 isn't blank, E2 will have the value of A2, followed by a space, followed by the value of D2, combined into one string.

In the end, your sheet should look something like this (I added headers to C:G, and you can do without F:G entirely if you use the "without lookup table" method for D):

enter image description here

0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .