Correcting Time Periods for Pivot tables

Hello All.
I am trying to come up with a formula that will help me correct certain data fields in an excel sheet. I am getting data that has metrics for stores during a certain period of time. I want to create a pivot table with this data but it is not correct yet. Stores A and B have the correct time period data for the pivot. Stores C and D need to show the the period that follows the existing period. Is there a formula or simpler way to do this? I am hoping to add an extra column with a formula that will display the existing period if the row is store A or store B, but will also display the following time period if the row contains store C or D. I have been manually correcting these time periods but the data is getting too large for that now. A sample is below. Store (column A), Period (Column B), and Correct Period (Column C). I am hoping to put the formula in column C and use that column for the pivot. I have attached a sample. Thank you so much!!!


Column A - Column B - Column C
Store - Period - Correct Period
Store A - FY15 Period 9 - FY15 Period 9
Store B - FY15 Period 10 - FY15 Period 10
Store C - FY16 Period 2 - FY16 Period 3
Store D - FY15 Period 3 - FY15 Period 4

You can use this formula in C2:

=IF(OR(A2="Store A",A2="Store B"),B2,LEFT(B2,12)&RIGHT(B2,2)+1)

then copy down.

Hope this helps.

Pete

Pete, thank you for the quick reply. The Periods represent months. Is there a way for this to work so that the formula puts out FY16 Period 1 when FY15 Period 12 is in column B?

Use this formula instead:

=IF(OR(A2="Store A",A2="Store B"),B2,IF(RIGHT(B2,2)="12","FY"&MID(B2,3,2)+1&" Period 1",LEFT(B2,12)&RIGHT(B2,2)+1))

Hope this helps.

Pete

Thanks Pete! Is it possible to give back FY15 Period 09 instead of FY15 Period 9?

This will change the Store A and Store B values to that format as well:

=IF(OR(A2="Store A",A2="Store B"),LEFT(B2,12)&TEXT(RIGHT(B2,2)+0,"00"),IF(RIGHT(B2,2)="12","FY"&MID(B2,3,2)+1&" Period 01",LEFT(B2,12)&TEXT(RIGHT(B2,2)+1,"00")))

Hope this helps.

Pete