Contextures

Contextures News 20181120

Excel Date Formula Challenge

November 20, 2018

Data entry popup, date 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: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.

Recent Birthdays

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.

Highlight Birthdays Last Week

For one solution, I used helper cells, date functions and conditional formatting.

  • At the top of the sheet, type a date in cell E2, or use a formula: =TODAY()
  • In cell F2, calculate the week number: =WEEKNUM(E2)
  • In cell G2, calculate the year: =YEAR(E2)

Then, to add the Conditional formatting:

  • Select all the cells with birthdates (B4:B18)
  • On the Excel Ribbon, click Conditional Formatting>New Rule
  • Click "Use a Formula to Determine Which Cells to Format"
  • In the Formula box, enter this formula, which refers to B4, because it is the active cell:
    • =WEEKNUM(DATE($G$2, MONTH(B4), DAY(B4))) =$F$2-1
  • Click Format, and select a fill colour, then click OK twice, to apply the formatting rule.

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.

Highlighted Birthdays Last Week

There's another solution in the sample file too. For more examples, go to the Conditional Formatting Examples page on my website.

Data Entry Popup

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.

Data Entry Popup List

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.

Excel Articles

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)

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

Paper Airplanes

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!

weekly photo

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.

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: November 17, 2018 4:29 PM