Time formula problem with its format when over 24 hours

[Honma Meiko]

Hi,

I have problem with time formula. when its duration over than 24 hours, it doesn't done properly. let say its duration was 161 hours but it display only 17 hours even i change format to [h]:mm:ss

Need your help,please. Thanks in advance.

[gmr4evr1]

In the format cells window, click custom and then click @, this worked for me when I tested it. Wait, this may do nothing for you. Can you show the formula you are using?

[MrShorty]

Understanding how Excel stores and uses date/time values (as serial numbers) is critical to understanding and debugging these kind of problems. I would suggest spending some time with this and similar tutorials: http://www.cpearson.com/Excel/datetime.htm#SerialDates

What I notice is that 161 hours is 1 week away (or 1 week + 1 day, depending on the start time). 17 hours is same day or next day, again, depending on start time. I would guess that, somehow, one of the dates you are subtracting is not correct. Double check the full date/time values that you are subtracting and make sure they are about a week apart and not same day/next day.

[joeu2004]

I have problem with time formula. when its duration over than 24 hours, it doesn't done properly. let say its duration was 161 hours but it display only 17 hours even i change format to [h]:mm:ss

The devil is in the details.

Please provide an example Excel file that demonstrates the problem. Use this forum's "attach file" feature. Or upload the file to a file-sharing website and post the public/share URL in a response.

[Pete_UK]

161 hours is 6 days and 17 hours - your format is showing only the number of hours. Use a custom format on that cell of [hh]:mm:ss - the square brackets ensure that the number of hours is not wrapped at 24.

Hope this helps.

Pete