# Contextures News 20180508

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!

### Split the Date

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.

Here are the formulas that I used to extract the date and time. See more examples for the SUBSTITUTE and SEARCH functions on my blog.

Date: =--LEFT(A2,SEARCH("\$",SUBSTITUTE(A2,"/","\$",2))+4)

• A 4-digit date always follows the 2nd slash, so SUBSTITUTE changes that to a \$
• Then, SEARCH gets the location of the \$, and add 4, to get the end of the date
• The LEFT function returns that many characters, starting at the left of the string
• The two minus signs (double unary) change the "text" date to a real date
• Format the result as Short Date

Time: =--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, TEXT(B2,"m/d/yyyy"),""),

"PM"," PM"),"AM"," AM")

• The inner SUBSTITUTE replaces the short date in A2 with nothing (an empty string "")
• The next SUBSTITUTE replaces any "PM" with " PM"
• The last SUBSTITUTE replaces any "AM" with " AM"
• The two minus signs (double unary) change the "text" date to a real date
• Format the result as Time

### Highlight Expiry Dates

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.

• A formula in column F calculates how many days until expiry: =D3-TODAY()
• A conditional formatting rule checks the result, and highlights if 30 days or less. =\$F3<=30
• A Summary sheet shows the total count of licences, and the number expiring soon.

### Excel Articles

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.

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

### Wind Storm

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.

I'll also post any article updates or corrections there.

Debra Dalgleish
dsdalg @ gmail.com

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

Last updated: June 1, 2018 2:20 PM