Next Date of Quarterly / Monthly / Weekly Reports

[Studpure]

Hi,
How can Excel calculate when the date of a Quarter / Month or Week will start?

Ideally, today's date is 5-Feb-2009

Some cell would come down to display the following:

Next week start: 09/02/2009
Next month start: 01/03/2009
Next quarter start: 01/04/2009

Any help will be great.

 

[ukmikeb]

Hi

If your reference date is in A1

For next week formula is :-
CHOOSE(WEEKDAY($A$1,2),0,6,5,4,3,2,1)+$A$1

For next month formula is :-
=DATE(YEAR($A$1),MONTH($A$1)+1,1)

For next quarter formula is :-
=DATE(IF(MONTH($A$1)<10,YEAR($A$1),YEAR($A$1)+1),IF((MONTH($A$1)+CHOOSE(MONTH($A$1),3,2,1,3,2,1,3,2,1,3,2,1))=13,1,(MONTH($A$1)+CHOOSE(MONTH($A$1),3,2,1,3,2,1,3,2,1,3,2,1))),1)

hth

Mike

 

[sanrv1f]

try this

Excel Workbook
	A	B	C	D
1		Next week	Next month	Next Qtr
2	05-Feb	09-Feb	01-Mar	01-Apr
Sheet4

Excel 2003

Cell Formulas
Range	Formula
B2	=A2+8-WEEKDAY(A2,2)
C2	=EOMONTH(A2,0)+1
D2	=EOMONTH(A2,3-MOD(MONTH(A2),3))+1

 

[Studpure]

Hi

If your reference date is in A1

For next week formula is :-
CHOOSE(WEEKDAY($A$1,2),0,6,5,4,3,2,1)+$A$1

For next month formula is :-
=DATE(YEAR($A$1),MONTH($A$1)+1,1)

For next quarter formula is :-
=DATE(IF(MONTH($A$1)<10,YEAR($A$1),YEAR($A$1)+1),IF((MONTH($A$1)+CHOOSE(MONTH($A$1),3,2,1,3,2,1,3,2,1,3,2,1))=13,1,(MONTH($A$1)+CHOOSE(MONTH($A$1),3,2,1,3,2,1,3,2,1,3,2,1))),1)

hth

Mike

This works great. Thanks.

Thank you ukmikeb and sanrv1f

 

[barry houdini]

For next quarter probably easier to use this formula

=DATE(YEAR(A1),CEILING(MONTH(A1),3)+1,1)