Microsoft Office Forums

Go Back   Microsoft Office Forums > >

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #3  
Old 02-09-2007, 07:10 AM
jhweaver jhweaver is offline
Novice
 
Join Date: Jan 2007
Posts: 6
jhweaver
Default Time in Excel

When calculating time, use normal formulas i.e. =A1+A2, etc

However, if the resulting sum exceeds 24 hours, you get some strange results. The way to correct this is to format the cell using CUSTOM formatting.

Set the cells holding the time(s) to normal hh:mm format, but in the cell where you want to calculate the totals set the cell format using custom to [h]:mm. This will correct the 24 hour problem.

Try this;

Put 7:45 into 4 consecutive cells (all formatted as hh:mm) and SUM the values. If the target cell (SUM) is formatted as hh:mm the answer will be 7:00

That's because the 4x7:45=31 hours
Because that is greater than 24 (hours), the difference is 7 hours (the answer).

If you change the target cells format to [h]:mm, it will now correctly answer 31:00 (31 hours)
Reply With Quote
 



Similar Threads
Thread Thread Starter Forum Replies Last Post
format cells to automatically place quotes around text dirtleg Excel 1 09-16-2008 01:52 PM
How to get cells to calculate time? How to stop correcting zeroes in cells ? kevin_ms Excel 4 09-14-2008 10:08 PM
Nested if formulat to merge cells cristi22 Excel 0 10-23-2006 08:28 AM
Automatically entering/fill data in cells in Excel 2003 dipdog Excel 0 08-17-2006 08:37 AM
How to count cells containing data and meet certain criteria AdamNT Excel 1 08-11-2006 11:51 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:37 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft