IMAGE: Excel In Seconds Tips and Tricks from The Excel Addict - Microsoft Excel 2003, 2007, 2010, 2013, 2016, 365
 TheExcelAddict.com
 
May 7, 2019
 
Hi fellow Excel Addict,
 
I hope you are having an excellent week.

I am still busily working away on my 'NON-EXCEL' project. As anyone who has tackled a renovation project knows, they always take a lot longer than you first expect. I was hoping to be finished by May 1 but now it looks like I'll be working on this until the middle of June.
The Excel Addict extracurricular activities

I did, however, take a break long enough to bring you another 'Excel in Seconds' tip.

But first...

Excel Dashboards Course Enrollment this week wtih 20% Discount

I just want to remind all my Excel Addict subscribers that Mynda is opening up registration for her Excel Dashboards course this week. You can get a 20% discount on the course if you sign up by May 16th. Just remember to enter the code EXCELADDICT on the Checkout page.
Two FREE Dashboard Webinars (one is BRAND NEW)

If you haven't seen any of Mynda's Dashboard webinars before, she is running two webinars this week. One is BRAND NEW!

Webinar 1:
Creating Excel Dashboards covers how to use Excel to build an interactive dashboard, no additional software or add-ins required, just plain old Excel and some data. This webinar targeted at Excel 2007/2010/2013/2016/2019/Office 365 users.


Webinar 2 (NEW):
Dashboards With Power Query & Power Pivot shows you how to use Power Query to get data from multiple sources, mash it up with Power Pivot and analyse it in PivotTables to create an interactive Excel Dashboard. This webinar is for Excel 2010/2013/2016/2019/Office 365 users.





Do you know anyone else who may be interested in learning about Excel Dashboards? Do them a big favour and forward this newsletter to them.

If you like my 'Excel in Seconds' tip this week, please share it and recommend your colleagues and any other Excel users you know to sign up for my newsletter.

Keep on Excelling,
Francis Hayes (The Excel Addict)
Email:  fhayes[AT]TheExcelAddict.com

Send Email. Connect With Customers. Grow Your Business.

With AWeber, you get all the email marketing tools you need to create and send beautiful and engaging emails. For a behind-the-scenes look at how you can use AWeber, sign up to our Test Drive email series:
 
Missed my last newsletter?

Click Here to View it Online


Having a positive attitude can help us in difficult times.
I hope today's quote will help you have a positive attitude today.

Quote of the Day

""If you care about what people think about you,
you will end up being their slaves.
Reject and pull your own rope."

-- Auliq Ice --

 
If you have a favourite quote, send it to me and I may post it in my newsletter.

THIS WEEK'S 'EXCEL IN SECONDS' TIP
 
A Formula To Find The Nth Nday Of Each Month

Sometimes you may need to find a date such as the 1st Monday of a month. You can use the following formula to do this.

 
 =DATE(A4,A3,1+((A1-(A2>=WEEKDAY(DATE(A4,A3,1))))*7)+(A2-WEEKDAY(DATE(A4,A3,1))))
(see alternate formula below)

This formula requires 4 input values.
 
A1 = Nth
A2 = Nday of the week (i.e. Sun=1, Mon=2 ... Sat=7)
A3 = Month
A4 = Year

In this example, we will determine the 1st Monday (i.e. 2) of May for the year 2019.

Formula For Nth Day Of Nth Week in Microsoft Excel 2007 2010 2013 2016 2019 365

To create a list of dates for all months, using the same inputs as above...

1) Enter 1 to 12 (i.e. months) down the column (G) to the left of your date formula column;

2) Edit the formula by changing the three references from cell A3 (i.e. the month) to cell G1;

3) While editing the formula, lock all other cell references by pointing to each one and pressing the F4 key to add dollar signs to both the column and row reference (e.g. $A$4);

4) Copy the formula down the column for all months.


Formula For Nth Day Of Nth Week For All Months in Microsoft Excel 2007 2010 2013 2016 2019 365
To copy these dates to another worksheet or workbook, Copy them and choose Paste Special, Values.
Nth Day Of Nth Week For All Months in Microsoft Excel 2007 2010 2013 2016 2019 365




Alternate Formula

After originally posting this tip, I received the following suggestion from Microsoft Excel MVP, Rick Rothstein with a shorter version of my formula above.

=DATE(A4,A3,1+7*A1)-WEEKDAY(DATE(A4,A3,8-A2))

For an explanation of Rick's formula plus a VBA option, click here.

To share this tip with your friends and
colleagues, choose one of these options...
 
A Formula To Find The Nth Nday Of Each Month

 
Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are EVER not completely satisfied with anything I recommend, please let me know and you will get your money...GUARANTEED. You can't lose.
 
 
 
"Spreadsheets Tips From An Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630