# Date for Month

1. ## Date for Month

How can we let our worksheet to determine the end of the month... eg..if I am creating in column A the daily dates... How can I let my worksheet to display the relevant dates only for the particular month .. ie.. January - up to 31/01/2007, February - up to 28/02/2007 and so forth...

P/S : For the months, I am using Data Validation to select month. So, I need my worksheet to be able to determine the last date of the month. I don't want to appear something like 31/02/2008...

On the other hand, how can we let the worksheet to display the day of the week in corresponding to the dates? For example : if the date changed to 01/12/2007 it will show Saturday.

2. When you select the month in the data validation, in a spare cell calculate the first day of the following month and then -1 from this date.

3. Thanks for that ... my issue is not so much on verifying what the user keyed in... but the daily dates are auto generated after selecting month and year. Hence, my issue here is more of how to tell the worksheet where to stop based on the month selected. I know the long way would be using IF functions... that means if it is not that month, the cell would be blank, and vice versa.. .. Just wonder if there is any better way (simplified way) to do similar task?

4. I have managed to resolve half of my problem here.... basically, I can get my worksheet to display or hide the dates according to the month selected. However, I still have problem in getting the worksheet to determine whether to display 31st day for months with 31 days. I have adopted the following formular, please help to comment see if there is any problem with this formula.

=IF(OR(MONTH("1-"&B3)=2,4,6,9,11),"",A36+1)

Where B3 is the months in word (using Data Verification)
A36 is date showing 30/Month/Year.

5. I finally managed to resolve the issue .. I have corrected my formula to be :-

=IF(OR(MONTH("1-"&B3)=2,MONTH("1-"&B3)=4,MONTH("1-"&B3)=6,MONTH("1-"&B3)=9,MONTH("1-"&B3)=11),"",A36+1)

However, I noticed this formula looks messy....anyone can show me a simplified formula without having the need for so many repetitions.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1