Excel Date Calculation

[Moonshine]

Hrmm, im sure there used to be an Excel forum, i seem to have lost it :/ Hope its ok posting here:

What im trying to do is, for a range of dates from 1/1/2005 to 31/12/2020 get the Qtrly Financial Period before...

So, for instance:

A date of 1/1/2005 makes the period before 1/10/2004 to 31/12/2005
A date of 1/5/2005 makes the period before 1/1/2005 to 31/3/2005
A date of 1/8/2010 makes the period before 1/4/2010 to 30/6/2010

Fiscal Period (for those that dont work with them) are, 1st April to 30th June, 1st July to 30th Sept, 1st Oct to 31st Dec and 1st Jan to 31st March of any year.

So what i want is 3 colums in excel, Column 1 = Period Start, Column 2 = Period End, Column 3 = Date

Can anyone figure the forumla out? Doesnt matter where its done in, just as long as the end result can be in Excel....

 

[neileg]

You need to use vlookup. Suppose you have a table of 1/4 date starts and ends in cells A12:C19. Column A has the start of the quarter date before, colum B is the start date of the actual quarter and column C is the end date. Suppose your date you want to match to quarters is in C2 and you want Start in A2 and End in B2.

The formula for Starts is:
=VLOOKUP(C2,$A$12:$C$19,2,TRUE)

The formula for Ends is:
=VLOOKUP(C2,$A$12:$C$19,3,TRUE)

 

[Moonshine]

Thanks for the Idea, finally got it working with:

=VLOOKUP(SUM(IF($A3<=Sheet2!$C$2:$C$73,IF($A3>=Sheet2!$B$2:$B$73,Sheet2!$A$2:$A$73)))-1,Sheet2!$A$2:$C$73,2,0)

Sheet2 data, like you say was just a lookup table, of teh possible periods and a unique number. Working fine now thanks

Scott

 

[neileg]

What's the SUM() doing in there?

 

[Moonshine]

Because your -1 to get the previous month, if i take it out it fails to work.