Hours Calculations

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

Can anyone help. I've heard that Excel is limited to calculating to a maximum
9,999:59 hours. Does anyone know how to overcome this without using decimal
hours and minutes. I work in the aircraft industry where the life is
calculated in hours and minutes and this can pose a problem for me. For
example, if an aircraft has used 9,900:30 hours and a component is changed
that has a life of 200:30 the aircraft hours that the new component is due
change is 10,101:00. Excel will not calculate this and only shows it as
'Value'. I need to show it in hours and minutes because people get confused
when they see decimal minutes 10.15 is taken as 10 hours 15 minutes and not
10 hours 25 minutes (10:25), this confusion can be critical.
 
First of all don't enter any time values using US regular number formatting
meaning a comma for thousands,
it will be returned as text thus a value error returned when calculated

enter it as 9900:30 and if you do it will be numeric (right aligned), then
if you
enter 200:30 in for instance B1 and 9900:30 in A1 and then use

=A1+B1 the result will be 10101:00:00 which is a correct time value.

If you need to enter 10101:00 in A2 you can't because it will be right
aligned and thus text
instead enter it as 10101/24 and format as time [hh]:mm
If you want to enter for instance 15250:45 then use

=15250/24+45/1440

which will return the correct time value.





--


Regards,


Peo Sjoblom
 
This is an odd problem because as you say if you enter 10000:00 it gets
treated as text but input 9999:59:59 and in another cell use =A1+TIME(0,0,1)
it works as wanted.

When I needed to do this I came up with this method

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit
Dim nNumColons As Long
Dim pos1 As Long
Dim pos2 As Long
Dim nHours As Double
Dim nMins As Double
Dim nSecs As Double

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

With Target

nNumColons = Len(.Text) - Len(Replace(.Text, ":", ""))
If nNumColons > 0 Then

pos1 = InStr(.Text, ":")
nHours = Val(Left(.Text, pos1))
If nNumColons = 2 Then

pos2 = InStr(pos1 + 1, .Text, ":")
nMins = Val(Mid(.Text, pos1 + 1, pos2 - pos1 - 1))
nSecs = Val(Mid(.Text, pos2 + 1))
Else

nMins = Val(Mid(.Text, pos1 + 1))
End If
If nHours >= 10000 Then

.Value = TimeSerial(9999, 59, 59)
.Value = .Value + TimeSerial(nHours - 9999, nMins - 59,
nSecs - 59)
Else

.Value = TimeSerial(nHours, nMins, nSecs)
End If
.NumberFormat = "[h]:mm" & IIf(nNumColons = 2, ":ss", "")
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.
 
I've pasted the code in but no change. It is a shame I can't copy the sheet
in the reply as it would be easier to see but I'll try and explain.
Basically I have a number of variables which give me my final hours
remaining figure.
These are the life of the component + current aircraft hours gives me the
initial due hours. Then I have the amount % of the life it can locally be
extended, this is then added to the due hours. These 2 are then repeated as
extensions can be granted at different levels of authority, this then gives
me the total % given and the due hours. Finally subtract the due hours from
the current aircraft hours leaves me hours remaining to change. For example.
Current aircraft hours 7349:00, the life is 150:00 which gives me due hours
of 7499:00 if no extensions are applied, if you then give it 10% at level 1
this gives you 15:00 to add onto 7499:00, which gives the new hours due at
7514:00, this is repeated again in the next 4 columns and then finally giving
the total due hours.

Bob Phillips said:
This is an odd problem because as you say if you enter 10000:00 it gets
treated as text but input 9999:59:59 and in another cell use =A1+TIME(0,0,1)
it works as wanted.

When I needed to do this I came up with this method

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit
Dim nNumColons As Long
Dim pos1 As Long
Dim pos2 As Long
Dim nHours As Double
Dim nMins As Double
Dim nSecs As Double

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

With Target

nNumColons = Len(.Text) - Len(Replace(.Text, ":", ""))
If nNumColons > 0 Then

pos1 = InStr(.Text, ":")
nHours = Val(Left(.Text, pos1))
If nNumColons = 2 Then

pos2 = InStr(pos1 + 1, .Text, ":")
nMins = Val(Mid(.Text, pos1 + 1, pos2 - pos1 - 1))
nSecs = Val(Mid(.Text, pos2 + 1))
Else

nMins = Val(Mid(.Text, pos1 + 1))
End If
If nHours >= 10000 Then

.Value = TimeSerial(9999, 59, 59)
.Value = .Value + TimeSerial(nHours - 9999, nMins - 59,
nSecs - 59)
Else

.Value = TimeSerial(nHours, nMins, nSecs)
End If
.NumberFormat = "[h]:mm" & IIf(nNumColons = 2, ":ss", "")
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--
__________________________________
HTH

Bob

Dave said:
Can anyone help. I've heard that Excel is limited to calculating to a
maximum
9,999:59 hours. Does anyone know how to overcome this without using
decimal
hours and minutes. I work in the aircraft industry where the life is
calculated in hours and minutes and this can pose a problem for me. For
example, if an aircraft has used 9,900:30 hours and a component is changed
that has a life of 200:30 the aircraft hours that the new component is due
change is 10,101:00. Excel will not calculate this and only shows it as
'Value'. I need to show it in hours and minutes because people get
confused
when they see decimal minutes 10.15 is taken as 10 hours 15 minutes and
not
10 hours 25 minutes (10:25), this confusion can be critical.
Click to expand...
Click to expand...
 
So are you saying that non of the individual component hours exceed
9999:59:59? If that is the case, I cannot see your problem, because adding
values less than 10000:00 that total over 10000:00 works fine.

--
__________________________________
HTH

Bob

Dave said:
I've pasted the code in but no change. It is a shame I can't copy the
sheet
in the reply as it would be easier to see but I'll try and explain.
Basically I have a number of variables which give me my final hours
remaining figure.
These are the life of the component + current aircraft hours gives me the
initial due hours. Then I have the amount % of the life it can locally be
extended, this is then added to the due hours. These 2 are then repeated
as
extensions can be granted at different levels of authority, this then
gives
me the total % given and the due hours. Finally subtract the due hours
from
the current aircraft hours leaves me hours remaining to change. For
example.
Current aircraft hours 7349:00, the life is 150:00 which gives me due
hours
of 7499:00 if no extensions are applied, if you then give it 10% at level
1
this gives you 15:00 to add onto 7499:00, which gives the new hours due at
7514:00, this is repeated again in the next 4 columns and then finally
giving
the total due hours.

Bob Phillips said:
This is an odd problem because as you say if you enter 10000:00 it gets
treated as text but input 9999:59:59 and in another cell use
=A1+TIME(0,0,1)
it works as wanted.

When I needed to do this I came up with this method

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit
Dim nNumColons As Long
Dim pos1 As Long
Dim pos2 As Long
Dim nHours As Double
Dim nMins As Double
Dim nSecs As Double

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

With Target

nNumColons = Len(.Text) - Len(Replace(.Text, ":", ""))
If nNumColons > 0 Then

pos1 = InStr(.Text, ":")
nHours = Val(Left(.Text, pos1))
If nNumColons = 2 Then

pos2 = InStr(pos1 + 1, .Text, ":")
nMins = Val(Mid(.Text, pos1 + 1, pos2 - pos1 - 1))
nSecs = Val(Mid(.Text, pos2 + 1))
Else

nMins = Val(Mid(.Text, pos1 + 1))
End If
If nHours >= 10000 Then

.Value = TimeSerial(9999, 59, 59)
.Value = .Value + TimeSerial(nHours - 9999, nMins -
59,
nSecs - 59)
Else

.Value = TimeSerial(nHours, nMins, nSecs)
End If
.NumberFormat = "[h]:mm" & IIf(nNumColons = 2, ":ss", "")
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--
__________________________________
HTH

Bob

Dave said:
Can anyone help. I've heard that Excel is limited to calculating to a
maximum
9,999:59 hours. Does anyone know how to overcome this without using
decimal
hours and minutes. I work in the aircraft industry where the life is
calculated in hours and minutes and this can pose a problem for me. For
example, if an aircraft has used 9,900:30 hours and a component is
changed
that has a life of 200:30 the aircraft hours that the new component is
due
change is 10,101:00. Excel will not calculate this and only shows it as
'Value'. I need to show it in hours and minutes because people get
confused
when they see decimal minutes 10.15 is taken as 10 hours 15 minutes and
not
10 hours 25 minutes (10:25), this confusion can be critical.
Click to expand...
Click to expand...
Click to expand...
 
Sory for the confusion. My last reply was just an example of how the sheet is
laid out because I cannot attach the working copy. I should have used a
better example. I have components that are due at 13567:00 which Excel
accepts as text even though the cell is formatted [h:mm], then you add the
various extension % granted to give you the final due time which comes out as
value.

Bob Phillips said:
So are you saying that non of the individual component hours exceed
9999:59:59? If that is the case, I cannot see your problem, because adding
values less than 10000:00 that total over 10000:00 works fine.

--
__________________________________
HTH

Bob

Dave said:
I've pasted the code in but no change. It is a shame I can't copy the
sheet
in the reply as it would be easier to see but I'll try and explain.
Basically I have a number of variables which give me my final hours
remaining figure.
These are the life of the component + current aircraft hours gives me the
initial due hours. Then I have the amount % of the life it can locally be
extended, this is then added to the due hours. These 2 are then repeated
as
extensions can be granted at different levels of authority, this then
gives
me the total % given and the due hours. Finally subtract the due hours
from
the current aircraft hours leaves me hours remaining to change. For
example.
Current aircraft hours 7349:00, the life is 150:00 which gives me due
hours
of 7499:00 if no extensions are applied, if you then give it 10% at level
1
this gives you 15:00 to add onto 7499:00, which gives the new hours due at
7514:00, this is repeated again in the next 4 columns and then finally
giving
the total due hours.

Bob Phillips said:
This is an odd problem because as you say if you enter 10000:00 it gets
treated as text but input 9999:59:59 and in another cell use
=A1+TIME(0,0,1)
it works as wanted.

When I needed to do this I came up with this method

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit
Dim nNumColons As Long
Dim pos1 As Long
Dim pos2 As Long
Dim nHours As Double
Dim nMins As Double
Dim nSecs As Double

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

With Target

nNumColons = Len(.Text) - Len(Replace(.Text, ":", ""))
If nNumColons > 0 Then

pos1 = InStr(.Text, ":")
nHours = Val(Left(.Text, pos1))
If nNumColons = 2 Then

pos2 = InStr(pos1 + 1, .Text, ":")
nMins = Val(Mid(.Text, pos1 + 1, pos2 - pos1 - 1))
nSecs = Val(Mid(.Text, pos2 + 1))
Else

nMins = Val(Mid(.Text, pos1 + 1))
End If
If nHours >= 10000 Then

.Value = TimeSerial(9999, 59, 59)
.Value = .Value + TimeSerial(nHours - 9999, nMins -
59,
nSecs - 59)
Else

.Value = TimeSerial(nHours, nMins, nSecs)
End If
.NumberFormat = "[h]:mm" & IIf(nNumColons = 2, ":ss", "")
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--
__________________________________
HTH

Bob

Can anyone help. I've heard that Excel is limited to calculating to a
maximum
9,999:59 hours. Does anyone know how to overcome this without using
decimal
hours and minutes. I work in the aircraft industry where the life is
calculated in hours and minutes and this can pose a problem for me. For
example, if an aircraft has used 9,900:30 hours and a component is
changed
that has a life of 200:30 the aircraft hours that the new component is
due
change is 10,101:00. Excel will not calculate this and only shows it as
'Value'. I need to show it in hours and minutes because people get
confused
when they see decimal minutes 10.15 is taken as 10 hours 15 minutes and
not
10 hours 25 minutes (10:25), this confusion can be critical.
Click to expand...
Click to expand...
Click to expand...
 
My code will allow input of hours greater than 10000 without converting to
text.

--
__________________________________
HTH

Bob

Dave said:
Sory for the confusion. My last reply was just an example of how the sheet
is
laid out because I cannot attach the working copy. I should have used a
better example. I have components that are due at 13567:00 which Excel
accepts as text even though the cell is formatted [h:mm], then you add the
various extension % granted to give you the final due time which comes out
as
value.

Bob Phillips said:
So are you saying that non of the individual component hours exceed
9999:59:59? If that is the case, I cannot see your problem, because
adding
values less than 10000:00 that total over 10000:00 works fine.

--
__________________________________
HTH

Bob

Dave said:
I've pasted the code in but no change. It is a shame I can't copy the
sheet
in the reply as it would be easier to see but I'll try and explain.
Basically I have a number of variables which give me my final hours
remaining figure.
These are the life of the component + current aircraft hours gives me
the
initial due hours. Then I have the amount % of the life it can locally
be
extended, this is then added to the due hours. These 2 are then
repeated
as
extensions can be granted at different levels of authority, this then
gives
me the total % given and the due hours. Finally subtract the due hours
from
the current aircraft hours leaves me hours remaining to change. For
example.
Current aircraft hours 7349:00, the life is 150:00 which gives me due
hours
of 7499:00 if no extensions are applied, if you then give it 10% at
level
1
this gives you 15:00 to add onto 7499:00, which gives the new hours due
at
7514:00, this is repeated again in the next 4 columns and then finally
giving
the total due hours.

:

This is an odd problem because as you say if you enter 10000:00 it
gets
treated as text but input 9999:59:59 and in another cell use
=A1+TIME(0,0,1)
it works as wanted.

When I needed to do this I came up with this method

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit
Dim nNumColons As Long
Dim pos1 As Long
Dim pos2 As Long
Dim nHours As Double
Dim nMins As Double
Dim nSecs As Double

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

With Target

nNumColons = Len(.Text) - Len(Replace(.Text, ":", ""))
If nNumColons > 0 Then

pos1 = InStr(.Text, ":")
nHours = Val(Left(.Text, pos1))
If nNumColons = 2 Then

pos2 = InStr(pos1 + 1, .Text, ":")
nMins = Val(Mid(.Text, pos1 + 1, pos2 - pos1 - 1))
nSecs = Val(Mid(.Text, pos2 + 1))
Else

nMins = Val(Mid(.Text, pos1 + 1))
End If
If nHours >= 10000 Then

.Value = TimeSerial(9999, 59, 59)
.Value = .Value + TimeSerial(nHours - 9999,
nMins -
59,
nSecs - 59)
Else

.Value = TimeSerial(nHours, nMins, nSecs)
End If
.NumberFormat = "[h]:mm" & IIf(nNumColons = 2, ":ss",
"")
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--
__________________________________
HTH

Bob

Can anyone help. I've heard that Excel is limited to calculating to
a
maximum
9,999:59 hours. Does anyone know how to overcome this without using
decimal
hours and minutes. I work in the aircraft industry where the life is
calculated in hours and minutes and this can pose a problem for me.
For
example, if an aircraft has used 9,900:30 hours and a component is
changed
that has a life of 200:30 the aircraft hours that the new component
is
due
change is 10,101:00. Excel will not calculate this and only shows it
as
'Value'. I need to show it in hours and minutes because people get
confused
when they see decimal minutes 10.15 is taken as 10 hours 15 minutes
and
not
10 hours 25 minutes (10:25), this confusion can be critical.
Click to expand...
Click to expand...
Click to expand...
 
I've pasted your code in and enabled the macro. The first time I got a syntex
error on this line: Value = .Value + TimeSerial(nHours - 9999, nMins - 59,
nSecs - 59)
After I took out the nSecs - 59, the error did not come up. My primary data
cells are E2:E55 which I've entered on the code instead of your example but I
still get value. The other time data cells are H2:H55, K2:K55 and O2:O55 that
all need to add up, the final total is in O2:O55

Bob Phillips said:
My code will allow input of hours greater than 10000 without converting to
text.

--
__________________________________
HTH

Bob

Dave said:
Sory for the confusion. My last reply was just an example of how the sheet
is
laid out because I cannot attach the working copy. I should have used a
better example. I have components that are due at 13567:00 which Excel
accepts as text even though the cell is formatted [h:mm], then you add the
various extension % granted to give you the final due time which comes out
as
value.

Bob Phillips said:
So are you saying that non of the individual component hours exceed
9999:59:59? If that is the case, I cannot see your problem, because
adding
values less than 10000:00 that total over 10000:00 works fine.

--
__________________________________
HTH

Bob

I've pasted the code in but no change. It is a shame I can't copy the
sheet
in the reply as it would be easier to see but I'll try and explain.
Basically I have a number of variables which give me my final hours
remaining figure.
These are the life of the component + current aircraft hours gives me
the
initial due hours. Then I have the amount % of the life it can locally
be
extended, this is then added to the due hours. These 2 are then
repeated
as
extensions can be granted at different levels of authority, this then
gives
me the total % given and the due hours. Finally subtract the due hours
from
the current aircraft hours leaves me hours remaining to change. For
example.
Current aircraft hours 7349:00, the life is 150:00 which gives me due
hours
of 7499:00 if no extensions are applied, if you then give it 10% at
level
1
this gives you 15:00 to add onto 7499:00, which gives the new hours due
at
7514:00, this is repeated again in the next 4 columns and then finally
giving
the total due hours.

:

This is an odd problem because as you say if you enter 10000:00 it
gets
treated as text but input 9999:59:59 and in another cell use
=A1+TIME(0,0,1)
it works as wanted.

When I needed to do this I came up with this method

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit
Dim nNumColons As Long
Dim pos1 As Long
Dim pos2 As Long
Dim nHours As Double
Dim nMins As Double
Dim nSecs As Double

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

With Target

nNumColons = Len(.Text) - Len(Replace(.Text, ":", ""))
If nNumColons > 0 Then

pos1 = InStr(.Text, ":")
nHours = Val(Left(.Text, pos1))
If nNumColons = 2 Then

pos2 = InStr(pos1 + 1, .Text, ":")
nMins = Val(Mid(.Text, pos1 + 1, pos2 - pos1 - 1))
nSecs = Val(Mid(.Text, pos2 + 1))
Else

nMins = Val(Mid(.Text, pos1 + 1))
End If
If nHours >= 10000 Then

.Value = TimeSerial(9999, 59, 59)
.Value = .Value + TimeSerial(nHours - 9999,
nMins -
59,
nSecs - 59)
Else

.Value = TimeSerial(nHours, nMins, nSecs)
End If
.NumberFormat = "[h]:mm" & IIf(nNumColons = 2, ":ss",
"")
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--
__________________________________
HTH

Bob

Can anyone help. I've heard that Excel is limited to calculating to
a
maximum
9,999:59 hours. Does anyone know how to overcome this without using
decimal
hours and minutes. I work in the aircraft industry where the life is
calculated in hours and minutes and this can pose a problem for me.
For
example, if an aircraft has used 9,900:30 hours and a component is
changed
that has a life of 200:30 the aircraft hours that the new component
is
due
change is 10,101:00. Excel will not calculate this and only shows it
as
'Value'. I need to show it in hours and minutes because people get
confused
when they see decimal minutes 10.15 is taken as 10 hours 15 minutes
and
not
10 hours 25 minutes (10:25), this confusion can be critical.
Click to expand...
Click to expand...
Click to expand...
 
I am sure Bob's solution works but did you try the non macro solution I gave
you
by entering large time values using a formula

=13520/24+45/1440

which would be

13520:45

when formatted as [h]:mm

also note that you cannot enter time values with comma signs like number
values when entered directly
like in your first example

9,900:30

will always be text whereas

9900:30

would work



--


Regards,


Peo Sjoblom

Dave said:
I've pasted your code in and enabled the macro. The first time I got a
syntex
error on this line: Value = .Value + TimeSerial(nHours - 9999, nMins - 59,
nSecs - 59)
After I took out the nSecs - 59, the error did not come up. My primary
data
cells are E2:E55 which I've entered on the code instead of your example
but I
still get value. The other time data cells are H2:H55, K2:K55 and O2:O55
that
all need to add up, the final total is in O2:O55

Bob Phillips said:
My code will allow input of hours greater than 10000 without converting
to
text.

--
__________________________________
HTH

Bob

Dave said:
Sory for the confusion. My last reply was just an example of how the
sheet
is
laid out because I cannot attach the working copy. I should have used a
better example. I have components that are due at 13567:00 which Excel
accepts as text even though the cell is formatted [h:mm], then you add
the
various extension % granted to give you the final due time which comes
out
as
value.

:

So are you saying that non of the individual component hours exceed
9999:59:59? If that is the case, I cannot see your problem, because
adding
values less than 10000:00 that total over 10000:00 works fine.

--
__________________________________
HTH

Bob

I've pasted the code in but no change. It is a shame I can't copy
the
sheet
in the reply as it would be easier to see but I'll try and explain.
Basically I have a number of variables which give me my final hours
remaining figure.
These are the life of the component + current aircraft hours gives
me
the
initial due hours. Then I have the amount % of the life it can
locally
be
extended, this is then added to the due hours. These 2 are then
repeated
as
extensions can be granted at different levels of authority, this
then
gives
me the total % given and the due hours. Finally subtract the due
hours
from
the current aircraft hours leaves me hours remaining to change. For
example.
Current aircraft hours 7349:00, the life is 150:00 which gives me
due
hours
of 7499:00 if no extensions are applied, if you then give it 10% at
level
1
this gives you 15:00 to add onto 7499:00, which gives the new hours
due
at
7514:00, this is repeated again in the next 4 columns and then
finally
giving
the total due hours.

:

This is an odd problem because as you say if you enter 10000:00 it
gets
treated as text but input 9999:59:59 and in another cell use
=A1+TIME(0,0,1)
it works as wanted.

When I needed to do this I came up with this method

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit
Dim nNumColons As Long
Dim pos1 As Long
Dim pos2 As Long
Dim nHours As Double
Dim nMins As Double
Dim nSecs As Double

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

With Target

nNumColons = Len(.Text) - Len(Replace(.Text, ":", ""))
If nNumColons > 0 Then

pos1 = InStr(.Text, ":")
nHours = Val(Left(.Text, pos1))
If nNumColons = 2 Then

pos2 = InStr(pos1 + 1, .Text, ":")
nMins = Val(Mid(.Text, pos1 + 1, pos2 - pos1 -
1))
nSecs = Val(Mid(.Text, pos2 + 1))
Else

nMins = Val(Mid(.Text, pos1 + 1))
End If
If nHours >= 10000 Then

.Value = TimeSerial(9999, 59, 59)
.Value = .Value + TimeSerial(nHours - 9999,
nMins -
59,
nSecs - 59)
Else

.Value = TimeSerial(nHours, nMins, nSecs)
End If
.NumberFormat = "[h]:mm" & IIf(nNumColons = 2,
":ss",
"")
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--
__________________________________
HTH

Bob

Can anyone help. I've heard that Excel is limited to calculating
to
a
maximum
9,999:59 hours. Does anyone know how to overcome this without
using
decimal
hours and minutes. I work in the aircraft industry where the life
is
calculated in hours and minutes and this can pose a problem for
me.
For
example, if an aircraft has used 9,900:30 hours and a component
is
changed
that has a life of 200:30 the aircraft hours that the new
component
is
due
change is 10,101:00. Excel will not calculate this and only shows
it
as
'Value'. I need to show it in hours and minutes because people
get
confused
when they see decimal minutes 10.15 is taken as 10 hours 15
minutes
and
not
10 hours 25 minutes (10:25), this confusion can be critical.
Click to expand...
Click to expand...
Click to expand...
 
Thanks for that, yours works fine once all the cells are formatted to [h]:mm

Peo Sjoblom said:
I am sure Bob's solution works but did you try the non macro solution I gave
you
by entering large time values using a formula

=13520/24+45/1440

which would be

13520:45

when formatted as [h]:mm

also note that you cannot enter time values with comma signs like number
values when entered directly
like in your first example

9,900:30

will always be text whereas

9900:30

would work



--


Regards,


Peo Sjoblom

Dave said:
I've pasted your code in and enabled the macro. The first time I got a
syntex
error on this line: Value = .Value + TimeSerial(nHours - 9999, nMins - 59,
nSecs - 59)
After I took out the nSecs - 59, the error did not come up. My primary
data
cells are E2:E55 which I've entered on the code instead of your example
but I
still get value. The other time data cells are H2:H55, K2:K55 and O2:O55
that
all need to add up, the final total is in O2:O55

Bob Phillips said:
My code will allow input of hours greater than 10000 without converting
to
text.

--
__________________________________
HTH

Bob

Sory for the confusion. My last reply was just an example of how the
sheet
is
laid out because I cannot attach the working copy. I should have used a
better example. I have components that are due at 13567:00 which Excel
accepts as text even though the cell is formatted [h:mm], then you add
the
various extension % granted to give you the final due time which comes
out
as
value.

:

So are you saying that non of the individual component hours exceed
9999:59:59? If that is the case, I cannot see your problem, because
adding
values less than 10000:00 that total over 10000:00 works fine.

--
__________________________________
HTH

Bob

I've pasted the code in but no change. It is a shame I can't copy
the
sheet
in the reply as it would be easier to see but I'll try and explain.
Basically I have a number of variables which give me my final hours
remaining figure.
These are the life of the component + current aircraft hours gives
me
the
initial due hours. Then I have the amount % of the life it can
locally
be
extended, this is then added to the due hours. These 2 are then
repeated
as
extensions can be granted at different levels of authority, this
then
gives
me the total % given and the due hours. Finally subtract the due
hours
from
the current aircraft hours leaves me hours remaining to change. For
example.
Current aircraft hours 7349:00, the life is 150:00 which gives me
due
hours
of 7499:00 if no extensions are applied, if you then give it 10% at
level
1
this gives you 15:00 to add onto 7499:00, which gives the new hours
due
at
7514:00, this is repeated again in the next 4 columns and then
finally
giving
the total due hours.

:

This is an odd problem because as you say if you enter 10000:00 it
gets
treated as text but input 9999:59:59 and in another cell use
=A1+TIME(0,0,1)
it works as wanted.

When I needed to do this I came up with this method

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit
Dim nNumColons As Long
Dim pos1 As Long
Dim pos2 As Long
Dim nHours As Double
Dim nMins As Double
Dim nSecs As Double

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

With Target

nNumColons = Len(.Text) - Len(Replace(.Text, ":", ""))
If nNumColons > 0 Then

pos1 = InStr(.Text, ":")
nHours = Val(Left(.Text, pos1))
If nNumColons = 2 Then

pos2 = InStr(pos1 + 1, .Text, ":")
nMins = Val(Mid(.Text, pos1 + 1, pos2 - pos1 -
1))
nSecs = Val(Mid(.Text, pos2 + 1))
Else

nMins = Val(Mid(.Text, pos1 + 1))
End If
If nHours >= 10000 Then

.Value = TimeSerial(9999, 59, 59)
.Value = .Value + TimeSerial(nHours - 9999,
nMins -
59,
nSecs - 59)
Else

.Value = TimeSerial(nHours, nMins, nSecs)
End If
.NumberFormat = "[h]:mm" & IIf(nNumColons = 2,
":ss",
"")
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--
__________________________________
HTH

Bob

Can anyone help. I've heard that Excel is limited to calculating
to
a
maximum
9,999:59 hours. Does anyone know how to overcome this without
using
decimal
hours and minutes. I work in the aircraft industry where the life
is
calculated in hours and minutes and this can pose a problem for
me.
For
example, if an aircraft has used 9,900:30 hours and a component
is
changed
that has a life of 200:30 the aircraft hours that the new
component
is
due
change is 10,101:00. Excel will not calculate this and only shows
it
as
'Value'. I need to show it in hours and minutes because people
get
confused
when they see decimal minutes 10.15 is taken as 10 hours 15
minutes
and
not
10 hours 25 minutes (10:25), this confusion can be critical.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top