Date Sensitive Formulas

[seenfresh]

I am attempting to create a date sensitive time table that looks like this:

Book1
	A	B	C	D	E
3	Date	Sep-06
4	9/1/06
5	9/2/06
6	9/3/06
7	9/4/06
8	9/5/06
9	9/6/06
10	9/7/06
11	9/8/06
12	9/9/06
13	9/10/06
14	9/11/06
15	9/12/06
16	9/13/06
17	9/14/06
18	9/15/06
19	9/16/06
20	9/17/06
21	9/18/06
22	9/19/06
23	9/20/06
24	9/21/06
25	9/22/06
26	9/23/06
27	9/24/06
28	9/25/06
29	9/26/06
30	9/27/06
31	9/28/06
32	9/29/06
Sheet1

Unfortunately I cannot include the cell A33 that contains 09/30/2006

I am trying to create a table that if I change the date in cell B3 the corresponding Column A dates will change according to the date in B3, which I have done...

The tricky part for me is that each calendar month has different end dates and I would like A formula that would cutt off at the end of each month thus not showing the next month if I drage the formula down... In other words I don't want to show this:

Book1
	A	B	C	D	E
29	9/26/06
30	9/27/06
31	9/28/06
32	9/29/06
33	9/30/06
34	10/1/06
Sheet1

Is it possible to create a function that would carry out something like this:

If(Index(A31:A34)) is equal to 10/1/2006 then enter "" blank cell

Also if I entered a new cell in C3 and somehow would change cell B3 to the next month I could refer to this cell in the above attempted formula?

Any insight or help would be much appreciated!

Thanks in advance

Sean

 

[onlyadrafter]

Hello,

In A4 enter

=IF(A4="","",IF(A4=EOMONTH(A4,0),"",A4+1))

and copy down as far as required.

 

[seenfresh]

Thanks

Thank you for your help!

I have entered the formula but a circular reference problem exists! Am I missing something?

Book1
	A	B	C	D	E
3	Date	Sep-06
4	1/0/00
5	9/2/06
6	9/3/06
7	9/4/06
8	9/5/06
Sheet1

Thanx

Sean

 

[onlyadrafter]

Hello,

OOPS!

Cell A3 should stay as =B3

 

[seenfresh]

This doesn't work here is what happens when I enter in cell A3=B3 and copy the formula from A4 down...

Book1
	A	B	C	D	E
3	Sep-06	Sep-06
4	1/0/00
5	1/0/00
6	1/0/00
7	1/0/00
8	1/0/00
9	1/0/00
Sheet1

Now here's what happens when I enter the formula in cell A5 which I think is where it should be entered if it is refering to the previous date in cell A4+1

Book1
	A	B	C	D	E
3	Date	Sep-06
4	9/1/06
5	#NAME?
6	#NAME?
7	#NAME?
8	#NAME?
9	#NAME?
10	#NAME?
Sheet1

I'm not sure what is happening

Thanks again!

Sean

 

[onlyadrafter]

wtf - bad afternoon

a3 should be =B3

A4 should have

=IF(A3="","",IF(A3=EOMONTH(A3,0),"",A3+1))

then copy this down as far as required.

Sorry - not concentrating.

 

[barry houdini]

EOMONTH is a function from Analysis ToolPak - if you don't have that installed you'll get a #NAME? error. You could try this formula instead for A5 copied down

=IF(A4="","",IF(DAY(A4+1)=1,"",A4+1))

 

[seenfresh]

Thank you gentleman it works great!

I am wondering if you might be able to briefly explain this formula

=IF(A4="","",IF(DAY(A4+1)=1,"",A4+1))

As I see it the Lookup value is A4 and if the value of cell A4+1 with reference to the Day function (which refers to proper calendar day within each specific month) is true then enter A4+1, but if not true then enter "" blank cell value, but I don't quite understanding the functional steps behind the formula... perhaps you might be able to explain it better?

thank you once again!

Sean