Contextures

Contextures News 20200630

Excel Date Formula Challenge

June 30, 2020

Date formula challenge, fix your user name, 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.

You'll get my next newsletter on July 14th -- we're on the summer schedule now, with a newsletter every 2 weeks.

Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.

Date Challenge

Here is little date formula challenge for you, in two parts. There is a solution below the screen shot.

A: If a list of dates is entered as numbers (YYYYMMDD), what formula will convert them to dates, starting in cell B2?

B: Can you do this without using the LEFT, MID, and RIGHT functions?

Solutions - Convert to Dates

The most common solution to this problem is to use the DATE function, with LEFT, MID, and RIGHT extracting the year, month and day numbers.

=DATE(LEFT(A2,4), MID(A2,5,2), RIGHT(A2,2))

Recently, UniMord shared a formula that's much shorter. The TEXT function formats the 8-digit number as "0000-00-00", and the 2 minus signs convert the result to a positive number.

=--TEXT(A2, "0000-00-00")

NOTE: This probably won't work for all regional settings, so test carefully before you use it in your workbooks. Unimord based this formula on a StackOverflow answer from Ron Rosenfeld.

See more Excel date functions on my Contextures website.

Show Your Name

In the Excel Options (General), you can enter a User Name, in the "Personalize your copy of Microsoft Office" section.

That name shows up if you insert a Note (old-style comments), and you can show that name with a macro, to personalize a message box.

user name in Excel note

Sometimes, though, Excel might show a different name, such as "Microsoft Office User", in your notes or macro message boxes. If that happens, try these steps to fix the problem:

  • Close Excel, and open Microsoft Word
  • Go to File > Options, General
  • If there is a check mark for the “Always use this nameā€¦” setting, clear that check box.
    • Even though Excel is the best Office program, Word is the boss of the User Name settings
  • Click OK, then close Word, and start Excel again.

user name settings in Word

Product Update

There's been an update to my Excel Search Form Builder (SFB), which is in the UserForms for Data Entry (UFDE) kit. In version 4.08 you can create a form with either 2 or 4 search boxes. See the details in my latest blog post.

To download the latest version, use the link that you got when you bought this product. If you can't find find that link, send me an email and I'll help,

Excel Articles

Here are a few Excel-related articles that you might find useful or interesting.

Dashboards -- There are lots of great tips and examples in this Dashboard Design Essentials article by Spencer Baucke (via David Napoli) (Level - Int/Adv)

Music: On the Kottke.org blog, watch Dylan Tallchief's video of making music in Excel. There's a link to the Excel file on Google Drive, so download it, and try it for yourself! (Level - Int/Adv)

Also see: My Excel Products || Previous Issues

Phase Two

Last week, our region entered Stage 2 of the provincial re-opening plan. Now you can go camping, get a haircut, cool off at a splash park, and enjoy other activities again. Restaurant patios are open too, but I'd rather have lunch on our own patio, where there's no problem with social distancing! And back here, nobody can see my pandemic hair. ;-)

weekly photo

NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2020/20200630ctx.html
I'll also post any article updates or corrections there.

That's it for this week! If you have any comments or questions, send me an email.

Debra Dalgleish
dsd@ contextures.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.

contextures newsletter info

 

Last updated: October 7, 2020 4:12 PM