Happy New Year! I hope you had a relaxing end to 2014, and great start to 2015.
If you’re working in Excel at the beginning or end of a year, dates can be a problem. Usually, you can enter the day and month, then press Enter, and Excel will add the year.
Most of the time that works well, but if you’re doing December month end reports in January, Excel will add 2015 – the current year. Remember to manually enter the year to the date, if it’s not the current year – 12/31/14. And that means typing 3 extra characters! Who has time for that?
On my wish list for Excel, I’d like to have an automatic year detector, similar to the century settings that we already have. You’ll find that option in the Windows Regional Settings:
The new setting could let you pick months in which the previous or next year should be applied. For example,
- If it’s December, and you enter a January date, use the next year.
- If it’s January, and you enter a December date, use the previous year
Date entry should be back to normal by the end of this month, and in the meantime, I’ll try to remember to include the year, when necessary.
Contextures Posts
Here’s what I posted recently:
- Use Excel to count hotel guests in a date range, based on their arrival and departure dates.
- December 27th was day 42000 in the Excel calendar. Were you geeky enough to notice that?
- For a humorous peek at what other people are saying about spreadsheets, read the latest collection of Excel tweets, on my Excel Theatre blog.
Other Excel Articles
Here are a few of the Excel articles that I read recently, that you might find useful.
- Collaborate with Bill Jelen (Mr. Excel), to create a book with the 40 greatest Excel tips
- Winston Snyder shows how to use names in a workbook, and asks if you ever abuse the INDIRECT function..
- The Power BI team at Microsoft announced new Excel 2013 BI stability and usability improvements in the December update..
- Finally – a use for 3-D charts. Gregory Long uses a 3-D column chart in Excel to create a background cityscape for illustrations. There are no audio instructions, so you can turn down the sound, if the music gets annoying.
- The Excel Support Team at Microsoft has an update on the “Form Controls stop working” problem, that was caused by a 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
- Diane Poremsky shares her Outlook macro that creates messages from data in an Excel file.
- Jeff Bennion shows how to use Excel for law firm billing, and you can apply these tips in other businesses too.
Share Your Events and Articles
If you read or wrote any other interesting Excel articles recently, or have upcoming Excel events, please let me know. Thanks!
_____________________