Category Archives: Date & time

How to get Week Numbers right in Excel using ISOWEEKNUM

Posted on by
3

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.

READ MORE

Highlight Weekends in Excel with Conditional Formatting

Posted on by
3
EasyExcel_37_1_Highlight weekends with conditional formatting

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:

READ MORE

Hide Future Dates in Excel with Conditional Formatting

Posted on by
2
EasyExcel_35_1_Hide future dates in Excel

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:

READ MORE

How to Summarize Hours in Excel and Show the Right Result

Posted on by
Reply

EasyExcel_17_1_Summarize Hours in Excel

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:

READ MORE