Finding year end applicable for each quarter

**Amar Thakkar** · 10-21-2011, 01:34 AM

I have a list of quarters as below
200203 200206 200209 200212 200303 200306 200309 200312

Also there are year ending dates as

200203 200303 200403 200503 200603 200703 200803

Dates are in YYYY MM format.

While using quarterly data for 200306, 200309 and 200312 .. I need to use annual data for 200303 as those are the latest annual numbers available with me.

So I want to find match year end and quarters in excel so that i can use formulae to continue with my work. Attached excel for better understading ..

**TMS** · 10-21-2011, 03:19 AM

=--(LEFT(E6,4)&TEXT($G$1,"00"))

Where G1 has the month for the Year End

Or, you could have a table of years and year end months in, say, I1:J4 and use:

=--(LEFT(E6,4)&TEXT(VLOOKUP(--LEFT(E6,4),$I$1:$J$4,2,FALSE),"00"))

See the attachment for details.

Regards

**Amar Thakkar** · 10-21-2011, 04:38 AM

Originally Posted by TMShucks

=--(LEFT(E6,4)&TEXT($G$1,"00"))

Where G1 has the month for the Year End

Or, you could have a table of years and year end months in, say, I1:J4 and use:

=--(LEFT(E6,4)&TEXT(VLOOKUP(--LEFT(E6,4),$I$1:$J$4,2,FALSE),"00"))

See the attachment for details.

Regards

Thanks a lot.. I can customize the second solution provided by you ..