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.
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:
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.
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.
(Thanks to Leonid Koyfman for letting me know about the dashboard)
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.
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)
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!
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: https://www.contextures.com/newsletter/excelnews2017/20170502ctx.html
ddalgleish @ contextures.com
Note: I am an affiliate for some of the products mentioned in this email, and earn a commission on the sales.
Last updated: July 21, 2017 9:39 AM