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.
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.)
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)
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?
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.
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.
That's it for this week! If there are topics that you'd like to see covered in future emails,
please let me know.
ddalgleish @ contextures.com
Note: I am an affiliate for some of the products mentioned in this email, and earn a commission on the sales.
Last updated: March 15, 2017 11:55 AM