# Date Sensitive Formulas

#### seenfresh

##### Well-known Member
I am attempting to create a date sensitive time table that looks like this:

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
ABCDE
299/26/06
309/27/06
319/28/06
329/29/06
339/30/06
3410/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

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Hello,

In A4 enter

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

and copy down as far as required.

Thanks

Thank you for your help!

I have entered the formula but a circular reference problem exists! Am I missing something?
Book1
ABCDE
3DateSep-06
41/0/00
59/2/06
69/3/06
79/4/06
89/5/06
Sheet1

Thanx

Sean

Hello,

OOPS!

Cell A3 should stay as =B3

This doesn't work here is what happens when I enter in cell A3=B3 and copy the formula from A4 down...
Book1
ABCDE
3Sep-06Sep-06
41/0/00
51/0/00
61/0/00
71/0/00
81/0/00
91/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
ABCDE
3DateSep-06
49/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

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.

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))

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

Replies
4
Views
264
Replies
5
Views
143
Replies
3
Views
160
Replies
8
Views
275
Replies
2
Views
577

Threads
1,217,433
Messages
6,136,601
Members
450,021
Latest member
Jlopez0320

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back