Three Frustrations When Calculating Time in Excel

Do you ever get frustrated when you attempt to perform time calucltaions in Excel? If so, you have plenty of company. Even simple formulas such as = End_Time - Start_Time can produce a "strange" result.

One trick - to apply the proper Custom Number format for the cells that contain your TIme Calculations.

Watch this video as I address three common frustrations with Time Calculations.

I invite you to visit my Excel Website
www.thecompanyrocks.com/excels
there you can view all of my video lessons and download many Excel Practice files.

Category:

Education

Tags:

• MS
• Microsoft
• Excel
• How
• to
• Perform
• Time
• Calculations
• Custom
• Number
• Formats
• IF
• Function
• MOD()
• Modulus
• Lessons
• Tutorial

[DannyRocksExcels]

@11keifferr

Well - I don't know what else to suggest - other than opening up a brand new Excel Workbook and starting fresh.

When I entered in the time values that you gave me, I got the correct answer. I could not find a way to replicate the incorrect results that you report.

Sorry!

Danny Rocks

The Company Rocks

[11keifferr]

@DannyRocksExcels

I tried Clear All and that did not work. I'm still getting 6.42 and not 6.25. I have used Excel 2003 as well as 2007 and I still get the same answer. I'm kinda stumped on this one.

[DannyRocksExcels]

@11keifferr

Not a problem - I think that I now know the "core problem" here:

1) When you delete a single cell or a group of cells, you ONLY delete the "contents of those cells" - text, numbers, formulas, etc.

2) Deleting a cell does NOT remove the "underlying" FORMATTING for those cells! And, that is where the problem lies in my experience.

If you are using Excel 2003 or older, select the cells and then go to the Edit Menu and select Clear - Clear All.

[11keifferr]

@DannyRocksExcels

I'm sorry to keep bothering you but you have been a great deal of help from watching your videos. I have completely wiped out all formulas and put all formulas back in and I still cannot get it to show 6.25. It keeps coming up as 6.42. I am not us AM/PM in the formatting. Is there something else that I may be doing wrong? All other decomals work( .5 and .75) but for some reason .25 will not work.

[DannyRocksExcels]

@11keifferr

I just entered this calculation in Excel and got 6.25 hours as the result. Here is my suggestion:

1) Do NOT mix AM/PM formatting with Military (24 hour) Time.

2) So, make the starting time - 10:00 and the ending time 16:25 and you should get the correct result - 6.25 hours.

Danny Rocks

The Company Rocks

[11keifferr]

@DannyRocksExcels

Ok...that worked on the 7.75 but when I put in 10:00 AM to 16:25 PM it comes up 6.42 in that same cell instead of 6.25 hours. Is there a way to make it work to where it shows .25, .5, .75 regardless of what time in enter?

[DannyRocksExcels]

@11keifferr

Excel is "storing" the correct result - e.g. 7.75 hours, However, to get Excel to display 7.75 hours rather than 7.8, you simply change the way that this cell is "formatted." In this case, if you add another decimal point to the formatting, you will see 7.75.

Easiest way to bring up the "Format Cells" Dialog box is to use the Ctrl + 1 (one) keyboard shortcut. In Number format, change to 2 decimal places.

Danny Rocks

The Company Rocks

[11keifferr]

Hi Danny!

I need to put 10 AM to 13:25 PM. I know that this is 3.25 hours, however, my formula puts it as 3.4. How do I show it as 3.25 hours? I also have to put 8:00 AM to 15:45 PM and it shows 7.8 instead of 7.75 hours. Thanks!

[DannyRocksExcels]

@guitarsforever

Yes, the difference between 10:00 AM and 1:30 PM is 3:30 - 3 hours and 30 minutes (presuming that the formula cell is "formatted" as h:mm. In order to have the formula result show as 3.5, do this: =(later time - earlier time)*24 and then format the resulting formula cell as a "number" with 1 decimal place.

Be sure to use the parenthesis () for the subtraction & then multiply* that result by 24.(hours)

That will get you the result you are looking for.

Danny Rocks

[guitarsforever]

Hi. I need to put 10 AM to 1:30 PM. I know that this is 3.5 hours, however, my formula puts it as 3:30. Thank you!