Home Archives for Custom Formats for Time in Excel
April 20, 2012 Leave a Comment

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 – http:shop.thecompanyrocks.com – 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 Perform Time Period Calculations in Excel [ 7:55 ] Play Now | Play in Popup | Download (1635)
Link to this post!
Filed Under: Excel Date and Time Functions, Excel Training, Format Cells in Excel, Podcast Tips & Timesavers Tagged: , , , , , , ,
April 3, 2009 Leave a Comment

Three Frustrations When Calculating TIme in Excel

When I am training a class to use MS Excel, I always get at least one question about performing time calculations. I sense that the person asking the question is frustrated. When I ask, “How many of you are frustrated when you try to perform a calculation to total time periods in Excel?” nearly every hand is raised! And they are right; Excel’s time calculations can cause frustration.

In this video lesson, I will show you how to overcome three common frustrations that you may have when performing time calculations in Excel.

Here are the steps to follow in this Excel Training Video:

  1. Format the cell that contains the formula =End_Time – Start_Time) to the Custom Number Format h:mm
  2. I encourage you to use this formula instead of a simple formula: =IF(End_Time < Start_Time, End_Time + 1, End_Time)-Start_Time.
  3. This will eliminate any possible “Negative times” which result in a cell filled with ######### A Negative Time is frequently the result of “Time Periods that Span Midnight. e.g. Start @ 11:00 PM and End @ 3:00 AM
  4. TIP: Use this Formula =MOD(End_Time – Start_Time, 1) This is the MODULUS Function.
  5. When you calculate the total number of hours and the result exceeds 24 hours, use this Format [h]:mm

Search My Index of Excel Video Lessons

 “The 50 Best Tips for …” is now available at the online store for The Company Rocks

Related Videos

Link to this post!
Filed Under: Everyday Calculations in Excel, Excel Date and Time Functions, Excel Functions, Excel Training Tagged: , , , , , ,
September 30, 2008 Leave a Comment

How to Solve Four Frustrations When Calculating Time in Excel

Have you ever been frustrated when you:

  1. Entered a “Time Value” in a cell only to find that Excel did not recognize it as a “time value?”
  2. Wrote a formula to calculate the elapsed time between “start and finish” only to find that Excel returned a “date” (e.g. 1/15/2008) and not a number?
  3. Wrote a formula to calculate the hours worked for your “Graveyard Shift” (those whose work hours cross Midnight) only to get a cell full of ########?
  4. Used the AutoSum function to calculate the total hours worked in a week only have Excel return a “silly” total like 1.7915?

Well, join the club! Most of my clients have experienced these four frustrations when they try to calculate “time values” in Excel. In this short video lesson, I will demonstrate how to solve each of these four frustrations.

I’d like to receive your feedback on this video lesson. Have you ever encountered this frustation? Did my explanation help you? What additional topics would you like me to cover?

You can email me – danny@thecompanyrocks.com

Here are the steps to follow in this video lesson:

  • Excel stores Time values as decimal numbers. Midnight is 0.00; 6:00 AM is 0.25; Noon is 0.5
  • Be careful when you enter “time values” into a cell. 12:00PM is considered “text.” You need to include a SPACE before the PM e.g. 12:00 PM in order to tell Excel that this is a “time value.”
  • Use this Keyboard Shortcut Ctrl+Shift+~ (Tilde) to see how Excel stores a “time value” in a cell.
  • When calculating “elapsed time”, use a “Custom Format” for  the cell with the Formula E.g. [h]:mm
  • When you calculate “elapsed time” for cells that “cross midnight” you will get ###### as your result unless you use a formula like =MOD((End_Time-Start_Time), 1)
  • When you total “time values” that will exceed 24 hours (E.g. Hours worked in a week) format the cell containing the formula with a “Custom Format” e.g.[h]:mm

Find the Excel Video Lesson that you want – Index to all Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007” is now availabe to purchase. I invite you to visit my online bookstore for more details.

Link to this post!
Filed Under: Everyday Calculations in Excel, Excel Date and Time Functions, Excel Training Tagged: , , , , ,