Contextures

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.

date and time with no separator

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.

highlight expiry dates

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.

Power BI Webinar

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!

weekly photo

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.

Debra Dalgleish
dsdalg @ gmail.com

Debra Dalgleish

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.

 

 

Get weekly Excel tips from Debra

 

pivot power free

 

 

 

 

 

Last updated: June 1, 2018 2:20 PM