Search Contextures Sites
Count in Date Range + more Excel tips
Excel Weekly News from Contextures January 6, 2015
In this week's Excel news, you'll see how to count activities in a date range, and much more. Thank you for reading the Excel news, and I hope that you are enjoying the articles. If you have suggestions for future topics, please let me know.
- Debra - firstname.lastname@example.org
- Count in Date Range
- Excel Day 42000
- More Excel Tips
- Video: ADDRESS Function
- Weekly Photo: Too Many Pictures
- Featured Excel Product
How many guests stayed at a hotel during a specific date range? It's a little tricky to figure out, but you can compare the guests' arrival and departure dates to the reporting period's start and end dates. You can do more complicated calculations too, to get the number of nights the hotel rooms were booked in a date range.
Click here to see the details, and download the sample file: Count Activities in Date Range
Excel stores dates as serial numbers, and over the holidays, I noticed that we hit day 42000, on December 27, 2014. We don't see those round numbers too often, so can you guess when day 43000 will arrive?
You can figure it out in Excel, or check the answer in my blog post, Day 42000 in Excel
Here are a few of the Excel articles that I read recently, that you might find useful:
- Winston Snyder shows how to use names in a workbook, and asks if you ever abuse the INDIRECT function..
- The Excel Support Team at Microsoft has an update on the "Form Controls stop working" problem, that was caused by a December security update. They're still working to find a better solution
- Wilson Peng uses the Concatenate function to help with market research and data analysis.
- On Microsoft's SMB blog, they posted 5 ultimate Excel productivity tips. Do you know all of them? If you do, the blog promises, "we'll eat our hat"
- Richard Baker, Professor of Clinical Gait Analysis at the University of Salford, shows how to create a Movement Analysis Profile in Excel, by overlapping 2 column charts
- Jeff Bennion shows how to use Excel for law firm billing, and you can apply these tips in other businesses too.
- Finally, for a humorous peek at what other people are saying about spreadsheets, read the latest collection of Excel tweets, on my Excel Theatre blog.
The ADDRESS function returns a cell address as text, based on a row number and column number. For example, you could find the address of the cell with the largest value in a range of cells. Watch this video to see how to use the ADDRESS function, and combine it with other Excel functions.
I hope you enjoyed some time with friends and family at the end of the year, and celebrated the beginning of 2015. We had our family here on Christmas Eve and Christmas morning, and had lots of fun.
Perhaps I took a few too many pictures though, as you can see in the photo below. My grandson and granddaughter seem reluctant to pose for one more!
If you don't have the time or energy to write your own Excel tips book, you can collaborate with Bill Jelen (Mr. Excel), to create a book with the 40 greatest Excel tips.
Bill is about to write his 40th Excel book, and he remembered that the Roman numeral for 40 is XL -- the short form for Excel! He set up a kickstarter project on Indiegogo, and you can contribute at different levels, to participate in the book's production. For example,
- for a $4 contribution, you can vote on the best tips and have your name listed in the acknowledgements
- for a $10 contribution, you can get the $4 perks, plus receive an ebook copy, as soon as it is finished
- for a $40 contribution, have your name included in the book's data sets, receive an autographed copy, and other perks
Check the 40 greatest Excel tips project page, for all the details.
See more recommended Excel products.
Note: I am an affiliate for some of the products mentioned in this newsletter, and earn a commission on the sales.
Contextures Inc., Copyright ©2015
All rights reserved.