Finance Calendar for Vlookup.

[XL_Newbie]

Hello Experts.

I need help. I am creating a vlookup table for large data which goes back a few years.
I like to add two columns for fiscal period and fiscal quarter/year. I am providing example of my manual work as a reference.

Please let me know what formula/function to use to automate the process. Thanks in advance for the help.

P.

[FDibbins]

What criteria are you using to identify the dates as P5, P6 etc and as Q1'11? The dates you have used in your example are all in late Nov to mid/late Dec, so I dont understand how you identify them as P5 Q1'11 etc. Perhaps if you could tell us your "rules" we could help better?

[XL_Newbie]

Thanks FDibbins for looking at this. I have a reference table which I am attaching. Please let me know if more info is needed. Thanks.

P.

[FDibbins]

Thanks for the update, but could you upload that info in a spreadsheet instead a .jpg? It makes it easier to wok out your references

[XL_Newbie]

Sorry, the JPG is all I have and was using that as a reference. I was hoping based on the info that I already started on the vlookup table that someone could give an idea on how I should go about completing that the rest.

Looks like I will need two different vlookup tables? Thanks.

[FDibbins]

No, I think have it, just 1 question...you refer to Q1'11, should that not be Q1'12?

[XL_Newbie]

No, I think have it, just 1 question...you refer to Q1'11, should that not be Q1'12?

You are absolutely correct. My bad there. Thanks.

P.

[FDibbins]

I think this gives you what you need. I have put conditional formatting on the "Q" part of it to easily identfy the quarters. With this as a base, you can hopefully do whatever else you need to do with the data. Let me know if you have any further needs

[XL_Newbie]

Really appreciated your help on this one FDibbins.

Sorry for not providing full details. The period is from P1 - P12 (one per month). Also, the Q4'12 should have been Q4'11 and should go back to Aug 31, 2011 where Q3'11 ended.
I will spend sometime going through your formaula and hopefully I can tweak to fix that myself. Again, thank you. I'll give you the star.

P.

[FDibbins]

Based on your additional info, I have modified the formulae to (I hope) meet your requirements

[XL_Newbie]

Based on your additional info, I have modified the formulae to (I hope) meet your requirements

Thanks again. Here's the table showing exactly the timeline for each period. The first period or P1 start in November of every year. The JPG I attached earlier shows starting and end date for each period.
I will try to see if I can modify your formalu for the period. the year works perfect. Thanks.

Please Login or Register  to view this content.

[FDibbins]

happy to help, let me know if you need anything else on this. If the problem has beem solved, please dont forget to mark it solved