Format Time
 

Could someone please tell me why I cannot format SumAll as [h]:mm? It gives
an answer of :03, when it should be 1618.20.

I'd also like to know how to format as Text "-[h]:mm" because I have some
other calculations that will result in a negative number of hours/minutes.

Thank you!

Function SumAll(rng As Range)
Dim cell As Range
SumAll = 0

For Each cell In rng
SumAll = SumAll + cell.Value
Next cell
SumAll = Format(SumAll / 86400, "[h]:mm") 'convert seconds to
hours/minutes
End Function

Format Time
 

If you are doing that as a worksheet function then you can't change any
attributes with function, just return a result.

If you want negative time, use the 1904 date system
(ToolsOptionsCalculation).

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"JustMe" wrote in message
. ..
Could someone please tell me why I cannot format SumAll as [h]:mm? It
gives
an answer of :03, when it should be 1618.20.

I'd also like to know how to format as Text "-[h]:mm" because I have some
other calculations that will result in a negative number of hours/minutes.

Thank you!

Function SumAll(rng As Range)
Dim cell As Range
SumAll = 0

For Each cell In rng
SumAll = SumAll + cell.Value
Next cell
SumAll = Format(SumAll / 86400, "[h]:mm") 'convert seconds to
hours/minutes
End Function

Format Time
 

why I cannot format SumAll as [h]:mm?



would "because it isn't an option with the format function" suffice.

Function SumAll(rng As Range)
Dim tot As Double
Dim cell As Range, dt As Double
SumAll = 0

For Each cell In rng
tot = tot + cell.Value
Next cell
dt = tot / 3600 ' converts second to hrs
hr = Int(dt)
Min = (dt - hr) * 60
SumAll = Format(hr, "0") & ":" _
& Format(Int(Min), "00")
End Function

If you don't want to truncate the seconds (which [h]:mm seems to do), then
remove the Int() part of the format argument.


--
Regards,
Tom Ogilvy


"JustMe" wrote in message
. ..
Could someone please tell me why I cannot format SumAll as [h]:mm? It
gives an answer of :03, when it should be 1618.20.

I'd also like to know how to format as Text "-[h]:mm" because I have some
other calculations that will result in a negative number of hours/minutes.

Thank you!

Function SumAll(rng As Range)
Dim cell As Range
SumAll = 0

For Each cell In rng
SumAll = SumAll + cell.Value
Next cell
SumAll = Format(SumAll / 86400, "[h]:mm") 'convert seconds to
hours/minutes
End Function

Format Time
 

Can you just use the function, then format the formula range accordingly?
Divide the function to get seconds if you need to.


Function SumAll33(sRng As Range) As Variant
SumAll33 = Application.Sum(sRng)
End Function

Then it makes what you are doing clearer.


Regards
Robert McCurdy

"JustMe" wrote in message . ..
Could someone please tell me why I cannot format SumAll as [h]:mm? It gives
an answer of :03, when it should be 1618.20.

I'd also like to know how to format as Text "-[h]:mm" because I have some
other calculations that will result in a negative number of hours/minutes.

Thank you!

Function SumAll(rng As Range)
Dim cell As Range
SumAll = 0

For Each cell In rng
SumAll = SumAll + cell.Value
Next cell
SumAll = Format(SumAll / 86400, "[h]:mm") 'convert seconds to
hours/minutes
End Function

Format Time
 

Thanks Tom. You Rock!


"Tom Ogilvy" wrote in message
...
why I cannot format SumAll as [h]:mm?



would "because it isn't an option with the format function" suffice.

Function SumAll(rng As Range)
Dim tot As Double
Dim cell As Range, dt As Double
SumAll = 0

For Each cell In rng
tot = tot + cell.Value
Next cell
dt = tot / 3600 ' converts second to hrs
hr = Int(dt)
Min = (dt - hr) * 60
SumAll = Format(hr, "0") & ":" _
& Format(Int(Min), "00")
End Function

If you don't want to truncate the seconds (which [h]:mm seems to do), then
remove the Int() part of the format argument.


--
Regards,
Tom Ogilvy


"JustMe" wrote in message
. ..
Could someone please tell me why I cannot format SumAll as [h]:mm? It
gives an answer of :03, when it should be 1618.20.

I'd also like to know how to format as Text "-[h]:mm" because I have some
other calculations that will result in a negative number of
hours/minutes.

Thank you!

Function SumAll(rng As Range)
Dim cell As Range
SumAll = 0

For Each cell In rng
SumAll = SumAll + cell.Value
Next cell
SumAll = Format(SumAll / 86400, "[h]:mm") 'convert seconds to
hours/minutes
End Function

Format Time
 

One mo

Option Explicit
Function SumAll(rng As Range)
Dim cell As Range
SumAll = 0
For Each cell In rng
SumAll = SumAll + cell.Value
Next cell
'convert seconds to hours/minutes
SumAll = Application.Text(SumAll / 86400, "[h]:mm")
End Function



JustMe wrote:

Could someone please tell me why I cannot format SumAll as [h]:mm? It gives
an answer of :03, when it should be 1618.20.

I'd also like to know how to format as Text "-[h]:mm" because I have some
other calculations that will result in a negative number of hours/minutes.

Thank you!

Function SumAll(rng As Range)
Dim cell As Range
SumAll = 0

For Each cell In rng
SumAll = SumAll + cell.Value
Next cell
SumAll = Format(SumAll / 86400, "[h]:mm") 'convert seconds to
hours/minutes
End Function

--

Dave Peterson

Format Time
 

Dave - Thanks for solving the "formatting as Text" part...you rock too! : )

"Dave Peterson" wrote in message
...
One mo

Option Explicit
Function SumAll(rng As Range)
Dim cell As Range
SumAll = 0
For Each cell In rng
SumAll = SumAll + cell.Value
Next cell
'convert seconds to hours/minutes
SumAll = Application.Text(SumAll / 86400, "[h]:mm")
End Function



JustMe wrote:

Could someone please tell me why I cannot format SumAll as [h]:mm? It
gives
an answer of :03, when it should be 1618.20.

I'd also like to know how to format as Text "-[h]:mm" because I have some
other calculations that will result in a negative number of
hours/minutes.

Thank you!

Function SumAll(rng As Range)
Dim cell As Range
SumAll = 0

For Each cell In rng
SumAll = SumAll + cell.Value
Next cell
SumAll = Format(SumAll / 86400, "[h]:mm") 'convert seconds to
hours/minutes
End Function

--

Dave Peterson

Format Time
 

In case you are not aware,

Application.Text

calls the built in worksheet function Text as if you put the formula

=Text(A1,"[h]:mm")

in a cell. So it is calling ouside of the VBA object model. Format
doesn't support it as previously stated.

--
Regards,
Tom Ogilvy






"JustMe" wrote in message
t...
Dave - Thanks for solving the "formatting as Text" part...you rock too!
: )

"Dave Peterson" wrote in message
...
One mo

Option Explicit
Function SumAll(rng As Range)
Dim cell As Range
SumAll = 0
For Each cell In rng
SumAll = SumAll + cell.Value
Next cell
'convert seconds to hours/minutes
SumAll = Application.Text(SumAll / 86400, "[h]:mm")
End Function



JustMe wrote:

Could someone please tell me why I cannot format SumAll as [h]:mm? It
gives
an answer of :03, when it should be 1618.20.

I'd also like to know how to format as Text "-[h]:mm" because I have
some
other calculations that will result in a negative number of
hours/minutes.

Thank you!

Function SumAll(rng As Range)
Dim cell As Range
SumAll = 0

For Each cell In rng
SumAll = SumAll + cell.Value
Next cell
SumAll = Format(SumAll / 86400, "[h]:mm") 'convert seconds to
hours/minutes
End Function

--

Dave Peterson