Contextures

Contextures News 20190521

Excel Date Formula

May 21, 2019

Date challenge, changing shapes, 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 June 4th -- we're 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.

Change a Shape

If you add a shape on an Excel worksheet, you don't have to delete it and start over, if you'd like to use a different shape instead. Here's how to change the existing shape to something different:

  • Click on the shape, to select it
  • At the top of Excel, click the Format tab (under Drawing Tools)
  • Click the Edit Shape command, then click Change Shape
  • Click on the new shape that you want to use.

change shape on worksheet

You can do this with legacy-style comments too, if you add the Change Shape command to your Quick Access Toolbar.

change comment shape

Go to my Contextures site, for more tips for working with comments

Date Formula Challenge

Someone asked me how to find the alert dates that are 7, 2 and 1 days before a target date. That seems easy enough, but there's a challenge -- if the alert dates fall on weekend (Sat/Sun), they have to be changed to the previous workday.

For example, if the target date is May 21, 2019, this calendar shows the alert dates - May 14, May 17, May 21. What formulas would you use to calculate the alert dates? See some solutions below the screen shot.

calender with alert dates

Original Solution (see shorter formulas below)

To get the alert dates, the formula subtracts the alert days (1, 2 or 7) from the target date. Then, it subtracts another 0, 1 or 2 days, using the WEEKDAY function. Here's the formula from cell B9 (alert 1 day before)

  • =$B$6-A9 - IF(WEEKDAY($B$6-A9,2)>5, WEEKDAY($B$6-A9,2)-5, 0)

formulas for alert dates

In WEEKDAY function, second argument is 2. That numbers days from Monday (1) to Sunday (7).

  • If the result is greater than 5, the date is a Saturday or Sunday
  • For those dates, subtract 5 from the weekday number, to get a 1 (6-5) or 2 (7-5)
  • Subtract the 1 or 2 from the calculated date, to get the weekday result
  • For other dates (non-weekend), zero days are subtracted

Shorter Formulas (thanks to UniMord)

  • =$B$6-A9-CHOOSE(WEEKDAY($B$6-A9),2,,,,,,1)
  • =$B$6-A9-INDEX({2,0,0,0,0,0,1},WEEKDAY($B$6-A9))
  • =$B$6-A9-SWITCH(WEEKDAY($B$6-A9),1,2,7,1,0) (Office 365 or 2019)

Read more about Date Functions on my Contextures site.

Excel Articles

Here are a couple of Excel articles that you might find useful or interesting.

Charts - If you've ever struggled to get a chart's labels set up nicely, read Jon Peltier's tale of working with his Gantt chart axis. If it's hard for a chart expert like Jon, no wonder the rest of us have trouble! (Level - Int/Adv)

Dynamic Arrays - If you use the new Dynamic Arrays in Excel, there might be problems if you send your workbook to someone using an older version of Excel. Get the free CheckDA tool from Charles Williams, to check for problems. Charles also sells a FastExcel add-in. (Level - All)

Excel Tips - Teacher Grainne Hallahan shares 5 Excel tips for teachers, and they're useful to anyone who uses Excel. Tip #4 is a time saver that I use sometimes. For Tip #3, use the number keypad to type the numbers. (Level - All)

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

Rockies

Last week, my daughter was on a business trip in the Canadian Rockies. Most of the time, she was hard at work, but did have an afternoon for fun and sightseeing. She sent this photo from a farm that they visited, with the beautiful mountains in the background. The tractor photo reminded me of the TV show - Green Acres (if you're old enough to remember that!)

weekly photo

NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2019/20190521ctx.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: May 21, 2019 12:03 PM