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:
- Format the cell that contains the formula =End_Time – Start_Time) to the Custom Number Format h:mm
- I encourage you to use this formula instead of a simple formula: =IF(End_Time < Start_Time, End_Time + 1, End_Time)-Start_Time.
- 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
- TIP: Use this Formula =MOD(End_Time – Start_Time, 1) This is the MODULUS Function.
- 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!
Speak Your Mind