Difference between two time values, where time values are [h]:mm format and greater than 24 hours

Solution 1:

How you enter the value is of prime importance.

As is documented by MS in Excel Specifications and Limits, "Largest amount of time that can be entered: 9999:59:59".

Any value that you have entered that is larger than that will be seen as text. Changing the numberformat of the cell does NOT convert that text string to a number.

In order to work with large time numbers, you can convert them into a time value that Excel will understand.

Therefore how you enter the values is most important.

The "safest" method would be to create a form where you enter the hours and minutes separately as integers, and then convert them to time with a mathematical formula.

They can then be formatted as time and you can add/subtract them as such.

If you are going to just type them into a cell in the format of h:mm, you can use a formula directly on that entry.

Excel stores times as fractions of a day. So the formula needs to:

• Check if the value is a number (in which case it needs no conversion
• If not a number, divide the hours portion by 24 (hours in a day), the minutes portion by 1440 (minutes in a day) and add the two.
• The result can then be treated as a time by excel.

This is not as robust as the user form method as it will allow you to enter "illegal" values, but for the data you present, the following formula will work:

=ABS(IF(ISNUMBER(A1),A1,LEFT(A1,LEN(A1)-3)/24+RIGHT(A1,2)/1440)-IF(ISNUMBER(B1),B1,LEFT(B1,LEN(B1)-3)/24+RIGHT(B1,2)/1440))