Format Time

[JustMe]

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

 

[Bob Phillips]

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
(Tools>Options>Calculation).

--
HTH

Bob Phillips

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

 

[Tom Ogilvy]

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.

 

[Robert McCurdy]

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

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

 

[JustMe]

Thanks Tom. You Rock!

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

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]

One more:

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

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

 

[JustMe]

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

One more:

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

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

 

[Tom Ogilvy]

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

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

One more:

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

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