How do I calculate 1000001 999999

Hours: minutes add over 10000: 01
authormessage
fuexchen2002
You can freely change the rank in the profile


Posted:
March 31, 2006, 11:45 pm
First name:

       

Hello everyone!

I hope you can help me. Here is my problem:

I have a machine that keeps track of its operating hours. Hours: Adding minutes is not the problem, but the machine already has over 15000: 00 operating hours on the clock and if I then want to add 1:23 hours, for example, the #Value! Error occurs.
If I start with 9999: 59 hours there is no problem.

Can someone help me?

Greetings Roman
rainberg
Excel users


Posted:
01 Apr 2006, 8:19 am
First name:


       

Hello Roman,

which cell formats do you use?

[hh]: mm

would be correct.

Or could it be that the cell containing the time to be added is formatted as text?
The error does not occur for me.
Table 1
 A.B.C.
19999:5901:2310001:22
Formulas of the table
 
fuexchen2002
You can freely change the rank in the profile


Posted:
01 Apr 2006, 5:23 pm
First name:

       

Yes, I use the format [hh]: mm

and as soon as I start with 9999: 59 I have no problem.

But I have to (want to) start with 19227: 34 and then it doesn't work anymore.

Greetings Roman
rainberg
Excel users


Posted:
01 Apr 2006, 5:42 pm
First name:

       

Hello Roman,

The following is written in the Excel Limitations.
Code:
Excel 97: 9999: 59: 59
Excel 2000: 9999: 59: 59
Excel 2002: 9999: 99: 99 => 9999: 59: 59
Excel 2003: 9999: 59: 59

The time 9999: 59: 59 corresponds to 02/19/1901 3:59:59 or 02/20/1905 3:59:59 if the 1904 date system is used.

To calculate with consecutive hours, the number format "[h]: mm" (or "[h]: mm: ss") is used. This means that hours greater than 23 are not converted into days. Since this number format represents a time, it is subject to the limitation described here. The largest number of hours that can be entered in a cell is 9999. Excel does not recognize the number 10000 and greater as the time and consequently it is displayed as text and treated as such (i.e. you cannot calculate with the "number").
However, times greater than 9999 can be calculated and displayed correctly. For example, the time is 9999: 00 * 2 = 19998: 00. Please note, however, that you cannot simply enter '= 9999: 00 * 2' in a cell (despite the correct time format), as this formula is invalid due to the colon character. If you enter '= 9999 * 2' instead, the result is 479952: 00, which is incorrect because Excel in this case regards the number 9999 as the number of days. In order for Excel to calculate correctly, the time must be written in inverted commas. The correct formula is therefore '= "9999: 00" * 2'. As an alternative, you could also write '= VALUE ("9999: 00") * 2'. Note that the formula '= VALUE ("10000: 00") * 2' returns the error value "#VALUE!" supplies.
The greatest existing time with consecutive hours is 71003183: 59: 59. It corresponds to the time 12/31/9999 23:59:59. As mentioned above, this number of hours cannot be entered directly in a cell, as the maximum permitted time is 9999:59:59. If the result of a formula is greater than 71003183: 59: 59, the familiar garden fence appears in the formula cell (255 times the # sign).
(Many thanks to Dr. Max Becke for his advice on this topic)

Danger:
The Microsoft Excel 2002 / XP reference states that the maximum permitted time is "9999: 99: 99". This information is clearly wrong. The specification should correctly read "9999: 59: 59".
fuexchen2002
You can freely change the rank in the profile


Posted:
02 Apr 2006, 4:11 pm
First name:

       

Thank you Rainer for your help.

Unfortunately, I can't figure it out very well, but it doesn't matter. I'll find a solution somehow (sooner or later)

Greetings Roman
Dancingdet
Beginners


Posted:
04 Apr 2006, 8:27 am
First name:
Place of residence: Moers

       

Hello fuexchen,

try Rainer's hint as follows:
 A.B.C.
119227:3401:2319228:57
219228:5701:3519230:32
Formulas of the table
A1: = "9227: 34" + "9000: 00" + "1000: 00"
C1: = A1 + B1
A2: = C1
C2: = A2 + B2
 
Related topicsForum / Topics reply author Calls last contributionExcel formulas: add values ​​if several conditions are met1wheinrichs1106828 Nov 2005, 12:46
c0bRa Excel formulas: cell reference / automatic addition4A-Man150820 Sep 2005, 10:46
guest Excel formulas: calculate time and hours2power user405103 Aug 2005, 10:20 pm
power-user Excel formulas: Adding cells with text content7Bugera1428Jul 20, 2005 at 8:25 am
rainberg Excel formulas: add hours5tom2ba249007 Jul 2005, 3:51 pm
fridgenep Excel formulas: Timesheet - time calculation (also negative), add up9blademoritz1637807May 19, 2005, 11:47 am
blademoritz16 Excel formulas: time in hours8Julilia479406 Apr 2005, 12:04 pm
fridgenep Excel formulas: add hours and minutes6Cougar47041March 03, 2005, 3:41 pm
Cougar Excel formulas: minutes in decimal number1JochenSR832Feb 18, 2005, 7:23 pm
Arnim Excel formulas: convert hours and minutes into number of working days with remainder13Kim3911Feb 10, 2005, 6:35 pm
Kim Excel formulas: Total calculation of failure in hours per system7twistx1948Jan 27, 2005, 8:58 pm
Thomas Ramel Excel formulas: Will add values ​​in relation to two other columns3Hardy_K_888197102 Dec 2004, 12:00
ae