Date Sensitive Formulas

seenfresh

Well-known Member
Joined
Jul 12, 2006
Messages
1,141
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
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

In A4 enter

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

and copy down as far as required.
 

seenfresh

Well-known Member
Joined
Jul 12, 2006
Messages
1,141

ADVERTISEMENT

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
 

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
:oops:

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. :oops:
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

Well-known Member
Joined
Jul 12, 2006
Messages
1,141
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,093
Messages
5,545,917
Members
410,711
Latest member
Josh324
Top