Note: All new – and existing – Excel Training Videos are now hosted on my main website. Click here to go directly to my Excel Training Section.

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

NEW! Download the Excel Practice file that I use in this Video Lesson:

• 3-frustrations-with-time-calculations

Find any Excel Video Training Lessons by Topic – Search My Index of Excel Lessons

New! Danny’s DVD Training Series, “The 50 Best Tips for …” is now available at the online store for The Company Rocks

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.

Related Videos

• Learn how Excel calculates time

• Learn how Excel calculates dates

Here are the steps to follow for this lesson:

1. To reveal the formula in a single cell, double-click the cell or press the F2 key.
2. To reveal all of the formulas in the worksheet use Ctrl + Tilde (Ctrl+˜) – This is a “toggle.”
3. There are 4 ways to write a formula to show the discounted price:
   1. If the cell containing the discount % is a negative (-20%) number =B5+(B5*$D$2)
   2. If the cell containing the discount % is a positive (20%) number =B5-(B5*$G$2)
   3. If the cell containing the discount % is a negative (-20%) number =B5*(1+$D$2)
   4. If the cell containing the discount % is a positive (20%) number =B5*(1-$G$2)

Find the video lesson that you want - Index to all Excel Topics

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

Related Videos

• Trust your sources but verify the accuracy of your data
  

Here are the steps to follow in this lesson:

1. To calculate the % of discount received: =”Savings”/”Original Price.”
2. Excel follows an “Order of Precedence” when performing calculations: It performs multiplication and division before performing operations involving addition and subtraction.
3. Enclose portions of your formula inside () in order to control the order of your calculations.
4. To determine the “Original Price” when you know the “Sale Price” and the “% of Discount”: =”Sale Price”/ (1-”% of Discount”)

Find the video lesson that you want – Index to all Excel Topics

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