Excel, displaying last quarter 2

[Sopman]

I input data from the previous quarter in a spreadsheet and I put the dates in a cell. Example: today I'm working on last quarter and the dates are, in this format, January 1 to March 31, 2003.

Is they a formula I can enter into a cell to have the previous quarter date displayed?

Sopman

 

[Dreamboat]

You could try setting a fill series. Try Tools-Options-Custom Lists... Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX

 

[SkipVought]

Hi,
If your dates are in column A with heading DATES, then in A2 enter the starting date and in the next cell, A3, enter...

=A2+1

and then copy that formula down until you have the range of dates that you want (thru row 91 I would surmize).

Now all you need to do is enter a new starting date when you want to do another quarter.

Hope this helps Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Sopman]

Skip,
I should not have said "dates". My spreadsheet is a template and in one cell, say A18, I want it to say 1 January to March 31, 2003.

When I open the template again on July 1st for the second quarter I want the date to read April 1 to June 30, 2003.

Sopman

 

[SkipVought]

Well there is two parts to your question.

1. What is a formula to calculate the previous quarter from the current date
A1 contains the current date
A2 contains first formula
B2 contains second formula
c2 contains third formula

=IF(MONTH(A1)<4,4,IF(MONTH(A1)<7,1,IF(MONTH(A1)<10,2,3)))
=IF($A2=0,&quot;January&quot;,IF($A2=1,&quot;April&quot;,IF($A2=2,&quot;July&quot;,&quot;Ocotber&quot;)))
=IF($A2=0,&quot;March&quot;,IF($A2=1,&quot;June&quot;,IF($A2=2,&quot;September&quot;,&quot;December&quot;)))

2. How can that quarter's date range be displayed.
[/code]
="1 "&B2&" to 1 "&C2&", "&IF(A2=3,YEAR(A1)-1,YEAR(A1))
[/code]
Hope this helps Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[xlbo]

This should do the trick for you:

=IF(MONTH(NOW())>9,"1st July to 30th September " & year(now()),IF(MONTH(NOW())>6,"1st April to 30th June " & Year(now()),IF(MONTH(NOW())>3,"1st January to 31st March " & year(now()),"1st October to 31st December " & year(now())-1))) Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[Sopman]

Thank you Geoff and Skip. They both work well.

Sopman