Adding hours and minute more than 10000 hours

[abatheo]

Dear,
I have a problem in excel and I need your assistance .
Here there is example of the question I have.
A1=00:20 means 0 hours and 20 minutes
B1=01:30 means 01 hours and 30 minutes
C1= 20000:00 means 20000 hours and 0 minutes
my question is to calculate D1 which should be equal
to C1-B1-(A1/5)


Best regards

 

[mole999]

my experience says that when I was adding values together I hit a maximal limit
> https://support.microsoft.com/en-gb/help/214386/maximum-times-in-microsoft-excel

 

[liveinhope]

I have a work round to convert entered values into a value that will add up. bit clunky but it seems to work
"type before" formula =TYPE(M2) "2's" are a problem and need to be turned into number
"temp" formula =(LEFT($M2,FIND(":",$M2,1)-1))/24 .. "#VALUE MEANS the original value was a number "
"hours " =IF(N2=1,M2,O2)
"type after " =TYPE(P2) .. note theyre all type 1 / numbers now

time entered	type before	temp	hours	type after	total
19999:00	2	833.2916667	19999:00:00	1	40835:55:00
24:00:00	1	#VALUE!	24:00:00	1
0:20	1	#VALUE!	0:20:00	1
12:36	1	#VALUE!	12:36:00	1
800:00:00	1	#VALUE!	800:00:00	1
9999:59:00	1	#VALUE!	9999:59:00	1
10000:00	2	416.6666667	10000:00:00	1

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

<strike></strike>

 

[liveinhope]

I've got a bit further with my idea to convert entered times > 10000 hrs into numbers .. removed helper columns

formula in O2 = =IF(ISNUMBER($M2),$M2,(LEFT($M2,FIND(":",$M2,1)-1))/24)

BUT it has a shortcoming ! - at the moment I am only converting the "hours" part into a number and ignoring "minutes" and "seconds"
as you can see my formula gives same result for the last 2 lines 10000:00 and 10000:30
Might be better to do this as VBA but I'm no expert there ..can anyone else help ?

time entered	isnumber?	TIME AS NUMBER	isnumber now	TOTAL "ENTERED"	10836:55:00
19999:30:00	FALSE	19999:00:00	TRUE	TOTAL "AS NUMBER"	50835:55:00
24:00:00	TRUE	24:00:00	TRUE
0:20	TRUE	0:20:00	TRUE
12:36	TRUE	12:36:00	TRUE
800:00:00	TRUE	800:00:00	TRUE
9999:59:00	TRUE	9999:59:00	TRUE
10000:00	FALSE	10000:00:00	TRUE
10000:30	FALSE	10000:00:00	TRUE

<tbody>
</tbody>

 

[liveinhope]

Ok I've had a go at doing this as a VBA custom function .. wasn't as hard as I thought
code works for the "hours" and "minutes" but it's not picking up "seconds' can anyone help


code is
Function timeasnumber(init_time) As Double
Dim tempstr As Variant
Dim thours As Double
Dim tmins As Double
Dim tsecs As Double

If IsNumeric(init_time) Then
timeasnumber = init_time
Else
On Error Resume Next
tempstr = Split(init_time, ":")
thours = tempstr(0) / 24
tmins = tempstr(1) / (24 * 60)
tsecs = tempstr(2) / (24 * 3600)
timeasnumber = thours + tmins + tsecs
End If
End Function


result - note that 10000:30:00 and 10000:30:59 return same result

time entered	timeasnumber vba	TOTAL "ENTERED"	10836:55:01
19999:30:00	19999:30:00
24:00:00	24:00:00	total vba	60837:25:01
0:20	0:20:00
12:36	12:36:00
800:00:00	800:00:00
9999:59:00	9999:59:00
10000:00:00	10000:00:00
10000:30:00	10000:30:00
10000:30:59	10000:30:00
00:00:01	0:00:01

<tbody>
</tbody>

 

[liveinhope]

*** ok I have a answer now VBA custom function *****

If the input hours are less than 10,000 function recognizes that this is already numeric/date and returns the same value

If the input hours are >= 10,000 function breaks it up into hours, minutes and seconds . Turns into a number and returns revised value

Function timeasnumber(init_time) As Double
Dim tempstr As Variant
Dim thours As Double
Dim tmins As Double
Dim tsecs As Double

If IsNumeric(init_time) Then
timeasnumber = init_time
Else
On Error Resume Next
tempstr = Split(init_time, ":")
thours = tempstr(0) / 24
tmins = tempstr(1) / (24 * 60)
tsecs = tempstr(2) / (CLng(60) * 60 * 24)
timeasnumber = thours + tmins + tsecs
End If
End Function


- enter this code into a module
- insert new column B
- enter formula in B2 , drag down =timeasnumber($A2)

- finally change your SUM formula to refer to B

time entered	timeasnumber vba		10836:55:01	<--- sum(a:a) wrong
19999:30:00	19999:30:00
24:00:00	24:00:00		60837:26:00	<-- sum(b:b) correct
0:20	0:20:00
12:36	12:36:00
800:00:00	800:00:00
9999:59:00	9999:59:00
10000:00:00	10000:00:00
10000:30:00	10000:30:00
10000:30:59	10000:30:59
00:00:01	0:00:01

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[rgsm]

Is there any update on this maximum limit for 10000+ hours to be entered directly into a cell?
We first encountered this problem some years ago (everything formatted [h:mm] as suggested above) but someone kindly wrote a macro that took the cell entry in "hrs.mins" (rather than hrs:mins - note decimal not colon), converted it in hidden cells, & then displayed hrs.mins in the original entry cell. This still allows real minutes, rather than decimal ones to be entered & displayed, so no confusion over conversion to decimal.

I don't have the skills to duplicate the macro process/cells etc in a new spreadsheet, which is required for calculating aircraft-critical maintenance, but would be grateful for a simpler way to format or convert an hrs:mins (OR hrs.mins) into a manageable form that will allow direct entry and calculations.
Any assistance/advice gratefully received.