flexi date

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

gilbert

Can anyone show me how to have a flexible date for my worksheet...it i
difficult to tell my problem in text. I have enclosed my worksheet fo
your perusal.....

Basically, I am looking for :-

1. A flexible date whereby it will show up to the maximum days of
particular month despite that I have maximum 31 rolls for months wit
31 days. Eg. In February, I want it to show until 28 and in leap year
I want it to show until 29. How should I go about it using Dat
function?

2. How can I let Excel automatically changes the day (ie. weekday) a
the dates changes ... eg. 16/3/2004 is Tuesday and if the date i
changed to 17/3/2004, I need Excel to return with Wednesday. I nee
this for multiple years as well.

Please help to advise

Attachment filename: shipment control sheets.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=47723
 
Hi
for all of this you may take a look at
http://www.cpearson.com/excel/datetime.htm

Specific for your questions:
1. If you have a date in A1 and want to know the number of day in this
month one way:
=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))

2. If your date is stored in A1 enter for example in A2
=A1
and format this cell (goto 'Format - Cells') with the custom format
DDDD
 
Hi Frank and Arvi,

I think both of you got what I meant wrongly for my first question. I
is difficult for me to explain in words here...please download th
attachment and take a look. Basically, I have 31 rolls (say A9 to A39
showing daily dates of chosen month. I need it to be flexible in a wa
that it will show only up to the maximum dates for that particula
chosen month. As in earlier example, if February 2004 is chosen, I nee
it to show the dates from 1/2/2004 until 29/2/2004. However, if th
year is change to 2005...it will show up till 28/2/2005 instead and n
29/2/2005 (cause this don't exist either!). The problem is how to g
about it, I tried with using IF function and it seems working fine bu
not perfect...it will still show the dates of the following months i
2005 is chosen instead of 2004.

Please advise how.

Thank you.

Rgds,
Gilber
 
Hi

Into A9 enter
=DATE(YEAR(TODAY(),MONTH(TODAY(),1)
Into A10 enter
=IF(MONTH(A9+1)=MONTH(A$9),A9+1,"")
and format as date
Copy A10 into range A10:A39

PS. Better always try to explayn what do yo want - I think most people here
don't open any attatchments. (And I myself avoid especially excelforum -
after I wasted a lot of time in vain, trying to access a couple of
attachments from there)
 
Hi
I prefere not to download attachments :-)
Try the following:
- A1 contains your month chosen: Enter this as normal date (e.g.
02-01-2004) and format this cell with the custom format MMMM YYYY

- If you want all days for this month shown in cells A2: Ax enter the
following in A2:
=DATE(YEAR(A1),MONTH(A1),1)

- in A3 (the second day) enter
=IF(MONTH(A2+1)<>MONTH($A$1),"",A2+1)
and copy this down for 31 rows. It will show only the valid days for
this specific month. Format these cells as date
 
Hi Frank,

Sorry, I have tried your way but still couldn't figure out...the cell
returned me with #value....I may be wrong somewhere

But, since you didn't download my workbook attached....you didn't
really get how my workbook worked.

Basically, I use data validation--list--for user to choose month in
cell A1, and validation--list--for user to choose year in cell B1.
B'coz cell A1 is text base, I have converted it into number base in
cell C1 (ie. if A1 is January, C1 will show 1, A1 is December, C1 will
show 12)

In cell A9, I have 1, A10 - 2, A11 - 3, A12 - 4 and etc until A39 - 31
In cell B9, I used Date function, that is in B9,
=date($B$1,$A$1,A9)....the result shown will be 1/1/2004 (assuming A1
is chosen as January and B1 is chosen as 2004).

How should I....using the formula as suggested to work on? I tried
=date(year(B1),month(A1),A9) in B9 hoping to see 1/1/2004 but it
doesn't....indeed it show #value....why?

Please help me, Frank....

Thanks.

Rgds,
Gilbert
 
Hi

When month is not current, but selected as you described to Frank, then the
formula for A9 will be
=DATE(YEAR(B1,MONTH(MATCH(A1,{"January","February","March","April","May","Ju
ne","July","August","September","Oktober","November","December"};0)),1)


--
(When sending e-mail, use address (e-mail address removed))
Arvi Laanemets


Arvi Laanemets said:
Hi

Into A9 enter
=DATE(YEAR(TODAY(),MONTH(TODAY(),1)
Into A10 enter
=IF(MONTH(A9+1)=MONTH(A$9),A9+1,"")
and format as date
Copy A10 into range A10:A39

PS. Better always try to explayn what do yo want - I think most people here
don't open any attatchments. (And I myself avoid especially excelforum -
after I wasted a lot of time in vain, trying to access a couple of
attachments from there)
Click to expand...
 
Hi
did Arvi's formulas work for you?. If yes, good :-)
If not please post back and I'll take a look at your attachment
 
Hi Frank...

Nope...there is error somewhere...and when I have corrected it, th
result shown is 1/1/1905. Why is that so? I hope I did not type i
erronously. Here is what I typed:-

=DATE(YEAR(E5),MONTH(MATCH(D5,{"January","February","March","April","May","June","July","August","September","October","November","December"},0)),1)

In this case, D5 is the month, E5 is the year.


Is there no other simple way to go about this? I am sure IF functio
could be of good use here....just than need some fine tune. Please tak
a look at my attachment
 
O.K.
try the following formulas:
B9: =DATE(E$5,G$5,1)
B10: =IF(B9<>"",IF(MONTH(B9+1)>MONTH($B$9),"",B9+1),"")
copy this down as far as you need

A9: 1
A10: =IF(B10<>"",A9+1,"")
and copy down
 
Back
Top