Excel Date Formula
May 21, 2019
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.
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:
You can do this with legacy-style comments too, if you add the Change Shape command to your Quick Access Toolbar.
Go to my Contextures site, for more tips for working with comments
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.
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)
In WEEKDAY function, second argument is 2. That numbers days from Monday (1) to Sunday (7).
Shorter Formulas (thanks to UniMord)
Read more about Date Functions on my Contextures site.
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)
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!)
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.
Last updated: May 21, 2019 12:03 PM