How to let Excel determine the end of the date?

[gilbert]

Gee...I need some tips here.

Can we customize an worksheet to a way that it will automaticall
determine the end of the period.

Assuming I have a worksheet containing the followings:-

Cell A1 = Period From
Cell B1 = 1/1/2004
Cell C1 = To
Cell D1 = 30/6/2004

Starting from column E, row 8, I have daily dates running until end o
the period, total 182 columns. (ie. D8=1/1/2004, E8=2/1/2004
F8=3/1/2004, etc)

Can we design the worksheet in such a way that it will automaticall
show the daily dates until the end of the period if we were to chang
the cell value in cell D1 to 31/3/2004 and showing only 91 column
instead of 182 columns? I want the worksheet to work in flexibl
way...ie. reflecting the period set.

Can we hide/unhide the unused columns as we change the period?

Please advise. Please let me know if you need a sample of my workshee
as I may not have well explain my problem

 

[Arvi Laanemets]

Hi

Into D8 enter the formula
=IF($B$1+COLUMN(D1)-COLUMN($D$1)>$D$1,"",$B$1+COLUMN(D1)-COLUMN($D$1))
and copy right for 182 columns

 

[Van]

Based on your dates being in cells b1 and d1, copy the following formula in D8 and copy across as many columns as you need:

IF($D$1<EOMONTH(DATEVALUE(TEXT($B$1,"mm/dd/yy")),-1)+COLUMN(D8)-3,"",EOMONTH(DATEVALUE(TEXT($B$1,"mm/dd/yy")),-1)+COLUMN(D8)-3

Also before you copy the formula in D8 make sure you format the cell for DATE or else you will get the date serial number

HT

Van

 

[gilbert]

I tried the two formulas but it doesn't seem working....hmm....but I
manage to use a more simple one use "IF" function. However, I can
instruct Excel to hide the blank column automatically nor ask Excel to
add extra columns to fit all the date periods.....that is to
say...supposing I hv 182 columns which will show all dates until June
30....and supposing I change my period date to March 31, (a total of 92
or 93 columns will be used only) I need Excel to automatically hide the
balance 92 columns up to avoid confusion...can we do that?

Please help if you know.