Excel Date Formula Challenge
June 30, 2020
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.
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.
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.
In the Excel Options (General), you can enter a User Name, in the "Personalize your copy of Microsoft Office" section.
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:
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,
Here are a few Excel-related articles that you might find useful or interesting.
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)
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. ;-)
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.
Last updated: April 4, 2021 8:57 PM