Trouble displaying daily dates in a given month

MROMAR

New Member
Joined
Jun 5, 2012
Messages
9
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
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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.

Thanks for your help though
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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