Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to format time in decimal format & calculate time | Excel Discussion (Misc queries) | |||
How to format time in decimal format & calculate time | Excel Discussion (Misc queries) | |||
How to format time in decimal format & calculate time | Excel Discussion (Misc queries) | |||
How to type format time in decimal format & calculate time | Excel Discussion (Misc queries) | |||
Remove time from a date and time field? Format removes the displa. | Excel Worksheet Functions |