Format Time

  • Thread starter Thread starter JustMe
  • Start date Start date
J

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
 
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)
 
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.
 
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
 
Thanks Tom. You Rock!


Tom Ogilvy said:
why I cannot format SumAll as [h]:mm?
Click to expand...



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 said:
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
Click to expand...
Click to expand...
 
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
Click to expand...
 
Dave - Thanks for solving the "formatting as Text" part...you rock too! : )

Dave Peterson said:
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
Click to expand...
Click to expand...
 
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 said:
Dave - Thanks for solving the "formatting as Text" part...you rock too!
: )

Dave Peterson said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top