Contextures

Contextures News

Excel End of Month Formulas

May 2, 2017

Create end of month formulas in Excel, and more, in this week's Excel news. Visit my Excel website for many more tips, tutorials and videos -- let me know if you're looking for something and can't find it there.

End of Month Formulas

There are lots of ways to calculate dates in Excel, and one of the most efficient is the End of Month (EOMONTH) function. I used it recently, to replace a long formula that someone was using to calculate month headings in a report.

To try it, put any date in cell B2. Then, in another cell, type this formula, to get the last day in that month:

=EOMONTH(B2,0)

  • The first argument is the start date
  • The second argument is the number of months before or after the start date. (Zero keeps the result in the same month)

You can use EOMONTH to find the start date for a month too. See more EOMONTH examples, and other date functions, on my Excel Date Functions page.

end of month formula EOMONTH

Amazing Dashboard Tricks

On his Clearly and Simply blog, Robert Mundigl shows an amazing interactive Excel dashboard. Usually you have to click on something to run a macro, but Robert makes things happen on the selected chart, when you simply hover over a data point with the mouse.

I downloaded the sample file, and the screen shot below shows what happened when I pointed to the Canada dot. It changed the line in the main chart, and also changed the information and charts at the right.

The workbook and code are unprotected, so you can poke around, to see how it works. And, try pasting in some of your own data, for more experiments.

If you aren't a programming expert, there are a few videos on my website that show how to get started.

interactive dashboard clearly and simply

(Thanks to Leonid Koyfman for letting me know about the dashboard)

Your Feedback

Thank you for sending your solutions to last week's problem -- finding a price based on invoice date and product name. Read my blog post with details on some of the formulas, and you can download the revised workbook too -- it has the solutions that I received before my blog post was published.

Excel Articles

Here are a couple of recent Excel articles that you might find useful.

Excel Skills - On Daily Dose of Excel blog, Dick Kusleika posted a few questions that he used to test a job applicant's Excel skills. On a similar topic, Nick Larsen has great advice on how developers can talk about themselves in interviews. (Level - All)

Excel Training -- Mike Girvin (ExcelIsFun) has a free two-hour video course, Excel for Accountants. It starts with some basics, and covers Pivot Tables, Power Query, functions, and more. Download the workbook to follow along, and go at your own pace. There is a list of topics, and times where they start in the video. (Level - All)

Also see: My Excel Products || Excel Events || Weekly Excel Humour

On the Lawn

Last week's photo was lovely pink flowers, and this week there are yellow flowers everywhere! I used to fight with them, but it's a losing battle, so they take over the lawn for a couple of weeks. My granddaughter loves the dandelions, and advised me to leave them alone, because they are important for the bees. So that makes me feel better!

weekly photo

That's it for this week! If there are topics that you'd like to see covered in future emails, please let me know.

NOTE: If you have trouble with the images or links in this email, paste this URL into your web browser, to see the online version: http://www.contextures.com/newsletter/excelnews2017/20170502ctx.html

Debra
ddalgleish @ contextures.com

P.S. You can choose to get the full Contextures news package (news and occasional special announcements), or the basic news package (news only). Click either link to change your news option.

Note: I am an affiliate for some of the products mentioned in this email, and earn a commission on the sales.

 

Search Contextures Sites

Excel Data Entry Popup List

 

 

 

 

Last updated: July 21, 2017 9:39 AM