Easiest Formula to hide dates not in a monthly calendar

[bearcub]

I'm creating a worksheet calendar and would like to hide the dates that are not in the current month.

For example, in my my January calendar I would like to show a blank cell where the Sunday and Monday dates of Dec 30 and Dec 31st don't appear (i.e. the cell is blank). I would also like the Feb dates not to show as well.

Is there a formula that would hide the dates not showing in the current month (non January 2019 dates in the January 2019 calendar).

Thank you for your help,

Michael

 

[Yongle]

Perhaps this helps:

The basic Excel formula to display only a January date is
=IF(MONTH(A2)=1,A2,"")

In a monthly calendar the Month is prominent
- presumably a different sheet for each month with month in the same cell (eg in F1)
If the value in F1 is a DATE eg 1 January 2019, formatted the way you want (eg to display January)
Can use that in the formula
=IF(MONTH(A2)=MONTH($F$1),A2,"")

Are your dates formula driven already
If so use your formula instead of A2
=IF(MONTH(your formula)=MONTH($F$1),your formula,"")

 

[bearcub]

Thank you for the info, this is helpful. However, when I follow your last formula I get errrors in the other cells and no dates show up when I apply it in the first row? I must be doing something wrong.

This is the formula that I'm using in the first row which works. Start references the date location (A1)

start-CHOOSE(WEEKDAY(start),0,1,2,3,4,5,6)

Is there a more efficient formula to use in the first calendar row?

Michael

 

[steve the fish]

Here is a way. In A1 type the first date of any month. In B1:H1 type Mon, Tue, Wed, etc. In A2:A7 Week 1, Week 2, Week 3 etc. Then in B2:

=IF(MONTH($A$1+(7*ROWS($A$1:A1))+COLUMNS($A$1:A1)-WEEKDAY($A$1,2)-7)<>MONTH($A$1),"",$A$1+(7*ROWS($A$1:A1))+COLUMNS($A$1:A1)-WEEKDAY($A$1,2)-7)

Drag across and down.

 

[Yongle]

@bearcub
I am confused by your formula

With date 31 December 2018 in cell A1, and A1 is named "Start"

This formula in any other cell always returns the SAME value
=Start-CHOOSE(WEEKDAY(Start),0,1,2,3,4,5,6)

Value returned is 30 December 2018 because
- CHOOSE(WEEKDAY(Start),0,1,2,3,4,5,6) always returns 1
- Start is always 31 December

 

[bearcub]

Hi Yungle, I got this formula from ExcelJet. That formula is just for 1st week. There is a separate formula for weeks 2-5.

The formula in this thread provides a January start date as Monday and not Tuesday. Am I doing something wrong?

Michael

 

[Yongle]

Am I doing something wrong?

The formula you posted does not work (see post#5)
Which formula are you using that returns 30 Dec, 31 Dec, 1 Jan, 2 Jan ...in adjacent cells?

 

[bearcub]

Hi Yungle,

Check out the article, this is how it works:

https://exceljet.net/formula/dynami...ail&utm_term=0_9a68797773-77b9086751-93484185

 

[Yongle]

You forgot to mention that you are using 2 formulas
You are also using something like this to get the dates to advance
=IF(B6<>"",B6,$H5)+1

I will look again when I have some time - probably early next week

 

[bearcub]

thank you, whenever you have time that would be great.