Archives for April 2012

How to Perform Time Period Calculations in Excel

Time Calculations in Excel

Time Calculations in Excel

Judging by the number of calls that I get from my viewers, working with Time Period Calculations is one of the most problematic challenges in Excel. My goal for this tutorial is to demonstrate how you can easily avoid the frustrations when you calculate elapsed time and total a series of time – e.g. hours worked during the week.

Apply a Custom Format to Formula Cells

When you create an Excel Formula that subtracts a Start Time from an End Time, you are likely to get a result that looks like 2:30 AM. This is not what you want to see! The solution is to apply a Custom Time Format – e.g. h:mm – to the formula cell(s).

Time that “Spans Midnight”

Many people work the “Graveyard Shift.” The start work late in the evening and they end work early the next morning. The problem that occurs when you want to calculate the number of hours worked is that Excel believes that you are performing a “negative time” operation and the result is a cell filled with ######## (Hash Marks). This happens because Excel thinks that both the Start Time and the End Time belong in the same day. To solve this, you could use an IF() Function formula. However, there is a much easier formula to use as I demonstrate in this video. It uses the MOD() Function.

Total Hours Worked

Use the SUM() function to total 8:00 hours worked each weekday. You are expecting to see 40:00 hours worked. So why does Excel return 16:00 hours instead? By default, Excel interprets 24:00 hours as a single day. So it subtracts 24:00 from the 40:00 hours worked to return 16:00 hours. Unless you make a formatting change to the formula cell you are going to have many unhappy and under paid employees.

The solution? Apply the [h]:mm Custom Formatting to the Formula Cells. Amazingly enough, Excel does not include [h]:mm as one of the Custom Formats in the list. So, simply edit one of the other formats and you will now have the correct answer displayed for your formula!

Learn More Tips for Excel

I invite you to visit my secure online shopping website – – to preview all of the resources that I offer including “The 50 Best Tips for Excel 2007” DVD-ROM.

Subscribe to the “Danny Rocks Tips and Timesavers” Video Podcast on iTunes

I offer a free video podcast for many of my video tutorials at the iTunes Store. Follow this link to see all of the Podcasts that I have posted. If you like what you see, I invite you to subscribe to this free service.

Watch Tutorial in High Definition

You can view this tutorial in High Definition on my YouTube Channel – DannyRocksExcels.


How to Roll Up a Summary by Month to Filter an Excel Pivot Table

Filter Using a Roll Up by Month Summary

Filter with a Roll Up by Month Summary

In this Excel tutorial, I respond to a viewer request. He likes the new “Roll Up Summary by Month” feature for filtering a field in an Excel 2007 – 2010 Field. What he finds frustrating – there seems to be no natural way to accomplish this with an Excel Pivot Table.

Natural Language Date Filters in Excel

Before I solve my readers dilemma, I demonstrate how to take advantage of the new “Natural Language” Date Filters that were introduced in Excel 2007. Date Filters allow you to filter records from “Today,” “Last Week,” “Next Month,” etc. They are available for Excel Tables and Excel Pivot Tables. These “Natural Language” Date Filters are a major improvement in Excel!

Group a Field for Pivot Tables

To solve my viewers question, I “Grouped” the original Date Field in his Pivot Table to produce “virtual” fields for “Month,” and “Year.” Now, it is a simple step to filter the “virtual” Month Field to obtain a “roll up” filter for individual months in the Pivot Table. Just select a single cell in the Pivot Table Date Field and choose Group Field. Make your choices in the Grouping Dialog Box and you are “good to go!”

I also show you how to take advantage of the Expand and Collapse Field Commands in a Pivot Table.

In-Depth Video Tutorial for Excel Pivot Tables

At my secure, online shopping website, you can purchase my 90-minute Video tutorial for Excel Pivot Tables. Available for immediate downloading or on a DVD-ROM. Version specific editions for Excel 2003, 2007, and 2010.

Watch Video Tutorial in High Definition

Follow this link to watch this tutorial in High Definition Mode on my YouTube Channel – DannyRocksExcels