Contextures

Contextures News

Excel Formula to Group Weeks

November 29, 2016

Calculate the week number or start date, and more, in this week's Excel news. Visit my Excel website for many more tips, tutorials and videos -- let me know if you're looking for something and can't find it there.

Week Grouping Formula

If you're building reports in Excel, you might need to group the results by week. Here are a couple of the Date Functions that I use.

In some tables, I use the WEEKNUM function -- handy for year over year comparisons: =WEEKNUM(A2)

For other reports, I use the week's start date, usually Sunday or Monday. This formula will calculate Sunday as the start date: =A2-WEEKDAY(A2+1,3)

The "3" at the end of that formula tells Excel to use the numbers 0 - 6 for Monday to Sunday. We adjust by one day, to subtract 0 if the date is a Sunday, 1 on Monday, etc.

CHALLENGE: How would you adjust the WEEKDAY formula, so the week starts on Saturday? (Answer in next week's newsletter.)

weekday and weeknum functions

Excel Dashboards

If you want to learn more about Excel dashboards, attend one of Mynda Treacy's free Excel Dashboard webinars this week. You'll pick up some amazing tips and skills during the 1 hour presentation.

Mynda has also opened registration for her highly-rated Excel dashboard course -- it's only available a few times each year. You can get a 20% early bird discount if you sign up by Thursday Dec 8th, at 8pm (Pacific time zone).

Also, as a bonus with the full dashboard course, I'll send you a free copy of my Data Entry Popup (DVMSP) kit -- just enter DebraD in the Coupon Code box when paying for Mynda's dashboard course. Then, email me your receipt, to let me know that you've signed up. (Note: this bonus is for the full course, not the free webinars)

Your Feedback

Hidden Rows: After reading about my hidden rows problem last week, Ron said he's had a problem with rows that were set to a height of zero. To show them again, he used the Row Height command, on the right-click menu.

Organizing: Rob M. asked for tips on organizing Excel files that you create for your own use, or for clients. I used to set up a main folder for each client, with project subfolders, and dated subfolders. Do you have any tips to share?

Excel Articles

Here are a couple of Excel articles I read recently, that you might find useful.

Excel Tips - On Microsoft's Office Blog, see how the owners of a small business use Excel to manage their business and personal finances. (Level - All)

Excel Blooper - Way back in 2002, a music video showed someone sending a text message from their mobile phone -- using Excel! (at the 3:17 mark) The musician finally explained why it happened. (Level - All)

Also, see all my Excel products on my Contextures website.

Mystery Solved

Thanks to Tracy M, who said the tree in last week's photo is a flowering crabapple.

After getting her tip, I looked through my old photos, and found one with the trees in full blossom. Those flowers matched the Robinson crabapple pictures that I found in a table of crabapple varieties (pdf file), so I guess that's what it is.

weekly photo

That's it for this week! If there are topics that you'd like to see covered in future emails, please let me know.

Debra
ddalgleish @ contextures.com

P.S. You can choose to get the full Contextures news package (news and occasional special announcements), or the basic news package (news only). Click either link to change your news option.

Note: I am an affiliate for some of the products mentioned in this email, and earn a commission on the sales.

 

Search Contextures Sites

Excel Data Entry Popup List

 

 

 

 

Last updated: March 15, 2017 11:55 AM