Excel has had the WEEKNUM function for as long as I can remember, but it is very confusing, and you tend to get it wrong more often than you get it right. This is what it looks like when you type the WEEKNUM function:
Who’s to know that it is actually the last option (21) that’s the right one for most users?
Thankfully, a few years ago, Microsoft finally launched the new ISOWEEKNUM function, which I’ll describe in a moment. But first, let’s look at the different ways to calculate week numbers.
The picture to the right shows a table with some sales figures for July. There’s nothing wrong with the table as it is, but I find it very hard to read and make sense of it. There are just a lot of numbers and dates, and you can’t even distinguish between weekdays and weekends. If we could highlight the weekends (or weekdays) it would be a lot easier to read these numbers. In this example I want to highlight the Saturdays and Sundays. Here’s how we’ll do it:
This is a very useful trick if you have a report showing dates and budget figures, and you want to make it more readable by making the future dates less visible.
Take a look at the report to the right. Assuming today’s date is 7/10, we want the dates from 7/1 through 7/10 to be easy to read, while the dates in the future should be hidden or greyed out. The trick is to not only dim the dates, but also the other columns on the same rows. This is an easy trick that you can apply on any report you get your hands on! Here’s how to do it:
Sometimes Excel doesn’t seem to add up the hours correctly. In this table I have made a table of an employee’s working hours and used the SUM function to add them together. Look what happens:
The sum is 13:45 instead of the expected 37:45. That’s 24 hours less than we wanted!
Here’s why: The result is actually 1 day, 13 hours and 45 minutes, but when you use the SUM function, Excel assumes that you want to keep the same format as above, which only shows hours and minutes.
How to fix it: