Excel Expiry Dates
May 8, 2018
Highlight expiry dates, try a formula challenge and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.
NOTE: You'll get the next newsletter on May 22nd -- I'm starting the summer schedule now.
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site!
How would you get the date from a cell that has the date and time mashed together, with no space between them? For an extra challenge, how would you get the time too?
You can download a sample file for the challenge, and my formulas are below the screen shot.
Time: =--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, TEXT(B2,"m/d/yyyy"),""),
"PM"," PM"),"AM"," AM")
If you use Excel to keep track of licences, or other things with an expiry date, how do you notice which ones are expiring soon?
To make them stand out, use conditional formatting, and highlight the dates which are within 30 days of the current date. I've created a new sample file that shows that technique, and you can download it on Excel Sample Files page. In the Conditional Formatting section, look for CF0008 - Expiry Date Warning.
Here are a couple of Excel articles that you might find useful or interesting.
Data Analysis - Save time with Chandoo's 35 shortcuts and tips for data analysis. There are tips for formulas, pivot tables, charts, and more. (Level - All)
Sad News - If you've used Excel for a while, you've probably visited Chip Pearson's Excel site. I've met Chip a couple of times, and was sad to learn that he passed away last month, after an auto accident. Chip created a vast collection of Excel tutorials -- date and time calculations, arrays, working with VBA, and many other topics. There are so many Excel treasures on Chip's site, and I'm grateful that he shared them.
Power BI - Join Mynda Treacy for her brand new free webinar - Power Query & Power Pivot Excel Dashboards. She'll build 2 interactive dashboards, for a quick overview of what you can achieve with the Power BI tools. The live webinars are only available until this Thursday, May 10, 2018.
Did you get that big wind storm on Friday? The Toronto area had lots of trees down, and furniture flying from apartment balconies, so it was rather dangerous for a while. Our trees stayed upright, and the shingles stayed on the roof, but our neighbours weren't so lucky. At least their tree fell away from their house!
That's it for this week! If you have any comments or questions, send me an email.
NOTE: For the online version, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2018/20180508ctx.html
I'll also post any article updates or corrections there.
dsdalg @ gmail.com
Last updated: June 1, 2018 2:20 PM