Excel Date Formula Challenge
November 20, 2018
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
Try this challenge - Here's a list of student names and birthdates, and we'd like to know which birthdays were celebrated in the previous week.
How would you mark those dates, so they're easy to see? To try the challenge, download the sample file, and my solution is below the screen shot.
For one solution, I used helper cells, date functions and conditional formatting.
Then, to add the Conditional formatting:
The formula calculates the week number for the birthdate (B4), using the current year (G2), instead of the birth year. Then it checks if that week number is one less than the week number in cell F2.
In cells where you have a data validation drop down list, macros can show a pop up list with the item list. To build a simple popup list for your Excel file, get my free Popup Listbox instructions and download the sample file. It's available in multi or single selection versions.
Popup Kit Update: If you've bought a copy of my Data Entry Popup kit (DVMSP), there's a new version (6.11) that you can download. It has a minor change - now you can list more that one column/rows/cell in the Setup "Exclude" options. For example, show a single selection popup everywhere, except columns 2, 3 and 6.
Use the download link from your purchase confirmation email, and if you can't find that, send me an email to ask for a new link.
Long Lists: If your workbook has really long lists, or lengthy items in the lists, my Data Entry Search Popup kit is a better choice. It has a search box to help you find items in a long list, and it shows long entries in a box at the top.
Both of the Popup kits let you build workbooks that you can freely share with your co-workers. They don't need a copy of the kit to use what you've built.
Here are a few Excel articles that you might find useful or interesting.
Functions - Liam Bastick shows how to use the LOOKUP function to pull data for a financial modelling workbook. The download link for the sample file is just above the second image. (Level - Int)
Excel Writers - I've never worked with them, but if you'd like to share your knowledge, the How-To Geek site is looking for freelancers to write Excel articles. They list the requirements, and have links to sample articles. No mention of the pay rate, but this Reddit article says it's negotiated. (Level - Int)
When our grandson comes to visit, we usually have a paper airplane competition, which is lots of fun. The photo shows a few models from our last event, and some flew better than others.
Next time, we'll try some of the designs from the Fold'N'Fly database of paper airplane designs. Use the check boxes at the left, to choose the features that you want. If you're celebrating Thanksgiving this week, this could liven up the party!
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2018/20181120ctx.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: November 17, 2018 4:29 PM