Show Each Date Within a Range

Hi Experts!

Hoping someone can help me out here with an interesting MS Excel problem as below:

I have three columns (sample data only below)

Start Date: (1/1/18)
End Date: (28/2/18)
Day of week: 5 (Friday)

Is there a way for me to calculate the precise date for every 'Friday' (in this example) within the date range?

I do not want the frequency or count of occurrences, I want the actual date associated with each Friday within the range

I would love to plot each occurrence on a Gantt style chart - your input would be greatly appreciated!

Hoping someone can help!


Matt NicholasBusiness AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


There are probably as many ways of doing this as there are people on this website, but the attached does what you have asked for.

If you change the parameters, then reset the filter.

Roy CoxGroup Finance ManagerCommented:
Here's another way
Matt NicholasBusiness AnalystAuthor Commented:
Thanks Roy and Alan,

Thanks for the speedy responses - Roy would you please explain the logic behind your solution? Since I would love to apply this to my own dataset. And would this still work if I were to display the numeral as .2..... and also with more than a single number ..34...?

Also if possible would you assist me in applying the solution via Power Query (context I am scraping a webpage to show airline flights which are typically shown as days of week in a .23..67 (Tuesday, Wednesday, Saturday and Sunday)
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Roy CoxGroup Finance ManagerCommented:
Hi Matt

I'll add some notes to the workbook.
Roy CoxGroup Finance ManagerCommented:
Sorry about the delay but I've had a busy day.

I've put some notes into the sheet, let me know if you need further help

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roy CoxGroup Finance ManagerCommented:
Some input from Matt would be appreciated
Matt NicholasBusiness AnalystAuthor Commented:
Thanks to both Roy and Alan for providing acceptable solutions to my problem and apologies for the delay!
Roy CoxGroup Finance ManagerCommented:
Pleased to help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.