Working out historic quarter end dates based on 1 date

Geordie

Board Regular
Joined
Oct 16, 2006
Messages
143
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
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

=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

Board Regular
Joined
Oct 16, 2006
Messages
143
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! :cool:

M
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

Well-known Member
Joined
Feb 24, 2003
Messages
1,760
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
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
ABCD
11helpercell
201/01/2006currentmonthcell
3
4Q105Q205Q305Q405
501/03/200501/06/200501/09/200501/12/2005
Sheet1
 

Watch MrExcel Video

Forum statistics

Threads
1,109,518
Messages
5,529,314
Members
409,863
Latest member
stacy09
Top