Question

The Forecast Dates sheet in the AD shell contains dates in the text format of “FY18Q2W1”.This means it is Fiscal Year 2018 (FY18), Quarter 2 (Q2), and Week 1 (W1).Make some formulas to convert these into Excel date formats. Your formulae should work for any date that I enter, even if it’s not in the provided list.That is, it should be able to berepeated. To simplify the problem, assume:•each quarter starts on the first of the first month of the corresponding quarter. So, Week 1 of quarter 4 is October 1. (This causes some problems for finding exact dates, but we’ll assume this for this assignment.)•Alldates will be from 2000 onwards. So FY18 refers to 2018and there will not be any 19XX dates.Finally, a couple hints:•Remember, break the problem up into smaller problems. Find the year. Then find the quarter. And so on. I use seven steps to calculate this. I could see using more or less than that, but definitely don’t try to do this in one bigcalculation.•The very first thing you should do in Excel is turn this into a Table (NOT A PIVOT TABLE) so you don’t have to copy calculations down every time.•MID, SEARCH, LEN, RIGHT, LEFT, and DATE are your friends. You haven’t met all of them yet, but you are meeting all of them now.•Seriously, don’t try to solve this in one equation.