how to get the difference of Hours for 2 cells which has value more 10000

Question

i want to get the difference value for below 2 cells

Answer 1

Dear @Shivanshu Sagar

I understand you are getting a #VALUE! error when attempting to calculate the difference between two time values exceeding 10,000 hours in Excel.

Excel has a built-in limitation where typing a time value with 10,000 or more hours (like 15609:33 or 11234:18) causes Excel to treat the entry as text instead of a valid time serial number. Subtracting text values directly results in a #VALUE! error.
Assuming your larger value 15609:33 is in cell A1 and 11234:18 is in cell B1, you can use the following formula to parse the text into decimal hours, calculate the difference, and format it properly:
Option A: Result in Hours (Decimal Format)

To get the difference as a simple number of hours (e.g., 4375.25 hours):

=(LEFT(A1; FIND(":"; A1) - 1) + RIGHT(A1; 2)/60) - (LEFT(B1; FIND(":"; B1) - 1) + RIGHT(B1; 2)/60)

Note: If your Regional Settings require a comma to separate function arguments, please adjust the formula to suit your system; for example, my machine requires a semicolon.

Option B: Result in [h]:mm Time Format

As the forum's AI recommended, to return the difference in time format (4375:15):

=((LEFT(A1; FIND(":"; A1) - 1) + RIGHT(A1; 2)/60) - (LEFT(B1; FIND(":"; B1) - 1) + RIGHT(B1; 2)/60)) / 24

Note: After entering this formula, press Ctrl + 1, select Custom, and set the cell format to [h]:mm.

How the Formula Works

• LEFT(A1, FIND(":", A1) - 1) extracts the hours portion (e.g., 15609).
• RIGHT(A1, 2) / 60 converts the minutes portion to a fraction of an hour (e.g., 33 / 60 = 0.55).
• Dividing the total difference by 24 converts the total hours into Excel's native time unit (days), which allows custom time formatting like [h]:mm.

Please let me know if you run into any issues or need further help adjusting this for your specific layout.

---

If the answer is helpful, please click "Yes" and kindly upvote it. If you have extra questions about this answer, please click "Comment".   

Note: Please follow the steps in the forum document to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

If the cells contain elapsed hours like 15609:33 and 11234:18, use a direct subtraction formula:

=A1-B1

If the result can cross midnight or become negative, use:

=MOD(A1-B1,1)

Then format the result cell so Excel can display hours greater than 24:

1. Select the result cell.
2. Go to Home > Format > Format Cells.
3. On the Number tab, choose Custom.
4. In Type, enter:

[h]:mm;@

That format is required because standard time formatting rolls over after 24 hours.

If the source cells are stored as text instead of real time values, subtraction can return #VALUE!. In that case, the values must first be converted to real time values before subtracting.

---

• Add or subtract time in Excel
• Need formula calculate difference between hours - Microsoft Q&A