List Every Wednesday of this month...how to do with a formula?

[PP3321]

The table contains weekday and day for June 2016

I need to list every Wednesdays' days automatically with a formula.

I can think of 2 possible solutions.

1. Use Index & Match?
2. Use WeekDay() Function?

Your guidance is greatly appreciated...

 

[PP3321]

I found this solution but was looking for something simpler...
http://www.mrexcel.com/forum/excel-questions/521495-date-every-sunday-month.html

 

[Naresh Yadav]

pls referred to attached file

1st Option:-=SMALL(IF($A$1:$A$29=C1,ROW($B$1:$B$29)),ROW(A1)) finish with CTRL+SHIFT+ENTER

2nd Option:-=AGGREGATE(15,6,$B$1:$B$29/($A$1:$A$29=$C$1),ROW(A1))

 

[PP3321]

Thank you so much!!! I will learn how to do this!!!!

 

[PP3321]

I found this when I was learning about Aggregate Function.
I wanted to share

 

[Nebu]

Hi:

Why do you need a formula for this if a pivot can do the same.


Thanks

 

[PP3321]

@Nebu
Sure but I am trying to automate the Excel template already developed by someone else. Before, it was entered manually. My job is to improve it by automating.
It is not easy to suggest to managers to use new template with pivot table because they want to stay with the current format.

 

[NARAYANK991]

Hi ,

But as people familiar with the power of Excel , it should be up to us to suggest better ways of doing our work ; if it means learning something new , it should be done , since if we do not embrace change , change will overcome us !

As far as automating is concerned , it cannot get any simpler than a pivot table , especially when it is used in conjunction with slicers.

If we compare Nebu's solution with the formula solution :

1. The formula solution requires the user to enter the desired day of the week ; this can be made easier by implementing a Data Validation dropdown , but there are disadvantages even in this route :

• The DV dropdown has to be implemented manually
• DV dropdowns do not allow for multiple selections
• DV dropdowns need to be recreated / extended when data is deleted or added ; of course , this objection does not apply in this case

2. The pivot table + slicer solution is totally automated , requiring only that the pivot table be refreshed if the input data changes.

• Slicers will automatically reflect the refreshed data
• Slicers allow for multiple selection

Narayan

 

[PP3321]

@NARAYANK991
1. I am adding the first date using VBA. So it is not a problem
2. I disagree.
After evaluating everything at every level,
I came to conclusion that it is better not to use Pivot Table in this particular case.
In business situations, we need to consider every factor.
Your point of view as Excel Developer is only 1 factor...