How to let Excel determine the end of the date?

  • Thread starter Thread starter gilbert
  • Start date Start date
G

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
 
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
 
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
 
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.
 
Back
Top