Trouble displaying daily dates in a given month

[MROMAR]

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.

 

[xladept]

Hi MROMAR,

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

=A5+1

 

[MROMAR]

Hi MROMAR,

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

=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.

 

[xladept]

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.

 

[MROMAR]

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

 

[xladept]

Hi MROMAR,

You're welcome.

 

[T. Valko]

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