# Trouble displaying daily dates in a given month

#### MROMAR

##### New Member
I have a sheet that displays the days of the month in cells A5:A35 based on the month and year. The month is refrenced is cell A2, and the year in B2. These two cells have drop down lists with A2 displaying the months (1,2,3...11,12) and B2 displaying the years 2012-2020.

The formula I am using starting from A5 is =DATE(\$B\$2,\$A\$2,1), A6 would use =DATE(\$B\$2,\$A\$2,2), A35 would use =DATE(\$B\$2,\$A\$2,31). With 31 being the maximum amount of days in a month, there are some months that have 30 and 28.

For these shorter months take June for example, B35 reads 7/31/12, the first day of July. Febuary would display the first 3 days of March. Is there a way to have values where the month does not match the month selected in A2 to come back as a blank, so that if I select June, it only shows me 30 days, but if I select Aug it will show 31 days.

Any help would be greatly appreciated.

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Hi MROMAR,

Instead of the formula in A6 - A35, you could write this:

Code:
``=A5+1``

Hi MROMAR,

Instead of the formula in A6 - A35, you could write this:

Code:
``=A5+1``

The issue here is how do I get rid of the July dates, I do not want to delete the cell with the formula, because the next month, July has 31 days and will need all 31 cells, as June only needs 30.

Hi MROMAR,

Use the code I suggested in cells a5 - a32 then:

<table border="0" cellpadding="0" cellspacing="0" width="128"><colgroup><col style="width:96pt" width="128"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;width:96pt" height="17" width="128">=IF(DAY(A32+1)<4,"",A32+1)</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">=IF(DAY(A32+2)<4,"",A33+1)</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">=IF(DAY(A32+3)<4,"",A34+1)</td> </tr> </tbody></table>

in the last three cells.

Last edited:
I was actually able to figure it out

for the 29th day of the month the formula was
=IF(MONTH(A5+28)=MONTH(A5),A32+1,"")

for the 30th day of the month the formula was
=IF(MONTH(A5+29)=MONTH(A5),A32+2,"")

for the 31st day of the month the formula was
=IF(MONTH(A5+30)=MONTH(A5),A32+3,"")

In a month like May, it shows all 31 days, in a month like Feb it will only show 28 or 29 days depending on if it's a leap year.

Hi MROMAR,

You're welcome.

I have a sheet that displays the days of the month in cells A5:A35 based on the month and year. The month is refrenced is cell A2, and the year in B2. These two cells have drop down lists with A2 displaying the months (1,2,3...11,12) and B2 displaying the years 2012-2020.

The formula I am using starting from A5 is =DATE(\$B\$2,\$A\$2,1), A6 would use =DATE(\$B\$2,\$A\$2,2), A35 would use =DATE(\$B\$2,\$A\$2,31). With 31 being the maximum amount of days in a month, there are some months that have 30 and 28.

For these shorter months take June for example, B35 reads 7/31/12, the first day of July. Febuary would display the first 3 days of March. Is there a way to have values where the month does not match the month selected in A2 to come back as a blank, so that if I select June, it only shows me 30 days, but if I select Aug it will show 31 days.

Any help would be greatly appreciated.
One way...

Enter this formula in A5:

=IF(COUNT(A2:B2)<2,"",DATE(B2,A2,1))

Enter this formula in A6 and copy down to A35:

=IF(A5="","",IF(DAY(A5+1) < DAY(A5),"",A5+1))<DAY(A5),"",A5+1))< p>

Format A5:A35 as Date

Replies
1
Views
225
Replies
3
Views
232
Replies
10
Views
736
Replies
2
Views
207
Replies
7
Views
413

1,211,984
Messages
6,105,219
Members
447,957
Latest member
Basildon

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back