Working out historic quarter end dates based on 1 date

[Geordie]

Hi,

Based on a date entered into the curent Month Cell:

Current Month 01-Sep-06

I want to be able to compute what the historical q-End dates for the last 4 quarters would be: E.g:


Q305 Q405 Q106 Q206

01-Sep-05 01-Dec-05 01-Mar-06 01-Jun-06

Can anyone offer any guidance on how to do this: So for example when the date is 01-Sep-06 I want the Quarter ends to stay as they are above but when I get to 01-Oct-06 I want the quarter ends to look like this:

Q405 Q106 Q206 Q306

01-Dec-05 01-Mar-06 01-Jun-06 01-Sep-06

The red cells are where i am hoping to puit my formulas based on the "Current Month" cell. (I already have formulas to work out the green bits but they are based on the red bits!)

Thanks in advance for any help!
Mandy

 

[Geordie]

Is this even possible -

~~~~Im Thinking of giving up!~~~~~~~

 

[Jonmo1]

ok, here's a shot at it.

given you enter your starting date in cell A2

first (current) quarter (this will be in b2)
=IF(TODAY()<EDATE(A2,3),EDATE(A2,3),EDATE(A2,6))

2nd quarter (in c2)
=EDATE(B2,3)

3rd quarter (in d2)
=EDATE(c2,3)

4th quarter (in e2)
=EDATE(d2,3)


Helpful?

 

[Jonmo1]

some of my formula for the current quarter is not showing up, even though I pasted it all there and it appeared in my window here while typing...
try again for the current quarter -

"=IF(TODAY()<EDATE(A2,3),EDATE(A2,3),EDATE(A2,6))"

Remove the quotes

The rest was all correct

 

[Jonmo1]

=IF(TODAY()"lessthansign"EDATE(A2,3),EDATE(A2,3),EDATE(A2,6))

Why is my less than sign not showing up in the previous posts?

so just replace "lessthansign" with an actual lessthan sign

 

[Geordie]

Hello - And many many thanks for your help with this! Tis driving me nuts!

Owww... I really wish i could download the software so I could display my excel sheet on this board.....

I think I have not explained what I am trying to do very well....

I'll have another shot:

I have a cell A1 where I manually type in the current month date. E.g. 01/10/2006. Based on this date I would like to try and work out what the last accounting Quarter end was...

So if I typed 01/10/2006 into Cell A1, Cell A2 would have been return 01/09/2006.

If I typed 01/09/2006 into Cell A1, Cell A2 would return 01/06/2006 etc...

(I worked out that to get the other 3 quarter ends I would just use the following formula based on Cell A2 output...

=DATE(YEAR(D11), MONTH(D11)-3, DAY(D11)) (e.g. last quarter - 3 months / -6 months etc to get the other historic quarters!)

On the up side if I can solve this one formula glitch then happy days the whole thing will work.....

It all sounds so simple!

M

 

[Jonmo1]

What is your baseline of what a quarter is? Standard quarters start on Jan1 Apr1 Jul1 Oct1. From what I gather now, you want to return the Previous end of quarter date based on entry in a cell.This can be done fairly easily once you define what a quarter is. From what you wrote previously it doesn't appear you are using the standard quarters, so you will need to define them.

Set aside 4 cells anywhere in your worksheet, and manually enter the Start Date of your 1st quarter. then fill to the right with
=EDATE(A1,3) to get 4 quarter start dates (or as far to the right as you would like to go... (a1 being the start date of your first quarter)

Then have another cell where you enter the date you want to calculate for.

For my example I used cells a1:d1 to define the quarters...
Cell a2 to enter the date to calculate for

then this formula in a3

=IF(A2>=D1,D1,IF(A2>=C1,C1,IF(A2>=B1,B1,IF(A2>=A1,A1))))


I'm probably way off the mark of what you want to do, but gave it a shot.

 

[jimrward]

Having done something similar to this many years ago on a mainframe computer in FORTRAN, i had to take time out and a few beers to come up with the following

given the current month date in cell A2 and a helper cell A1 (to stop the need for repeating the formula in the other cells)

The following calculates the Green and red cells for you, probably not what you wanted but kept my brain ticking over

quarters.xls
	A	B	C	D
1	1	helpercell
2	01/01/2006	currentmonthcell
3
4	Q105	Q205	Q305	Q405
5	01/03/2005	01/06/2005	01/09/2005	01/12/2005
Sheet1