Excel time formatting problem

John, one solution is to use a custom function instead of cell formatting. As in:



Put this in a standard code module then instead of a sum formula use:

=CustomTimeFormat("A1:A10")

(for instance)

Or simply use built in formulas to achieve same result (probably a better idea)

see http://groups.google.com/group/micr...ours+with+comma&rnum=8&hl=en#0d66ad1346297da0

Thanks scifibum. Your code is a bit over my head so I'm looking forward to figuring out exactly why this works. I'll try it out this weekend.


John

This may be ugly, but I believe it "delivers..." Try this formula:
=TEXT(INT(F2)*24,"#,##0")&":"&TEXT(MOD(F2,1),"00")
Where F2 cell contains your un-comma'ed value.
Note: The result will look correct, but will NOT be anything more
than uncalculable text. /s/ Larry

This may be ugly, but I believe it "delivers..." Try this formula:
=TEXT(INT(F2)*24,"#,##0")&":"&TEXT(MOD(F2,1),"00")
Where F2 cell contains your un-comma'ed value.
Note: The result will look correct, but will NOT be anything more
than uncalculable text. /s/ Larry
OOPs... not quite right on minutes...

OK --- Working from scifibum's original code, I came up with the following:

Function TimeFormatWithComma(DataPoint)

Dim Hours As Integer
Dim Minutes As Integer

On Error GoTo ErrorProcess

Hours = Int(DataPoint * 24)
Minutes = ((DataPoint * 24) - Hours) * 60
TimeFormatWithComma = Format(Hours, "#,##0") & ":" & Format(Minutes, "00")

Exit Function

ErrorProcess:
TimeFormatWithComma = "Error!"

End Function

Since I am using DataPoint in other calculations, I decided to do the summation outside of the function so that it could be used elsewhere. I just take the unformatted summation result as a data point and pass it through the formatting function which renders it more readable, but no longer usable as an argument in other formulae. For further calculations, I just go back to the original data point.

I set the formatting to hide leading zeros in the hours value, but always show both positions in the minutes value as suggested by indiantrix.

I removed the rounding function as it seems unnecessary. To my surprise, Excel apparently rounds seconds up and down automatically. On my system, 2800:00:29 (formatted with [h]:mm:ss) comes out of the TimeFormatWithComma function as 2,800:00. 2800:00:30 comes out as 2,800:00. 2800:00:31 comes out as 2,800:01.

I've tested this on my data and it works just fine. Thanks to everyone for all your help.


John

Woo Hooooooo!

I've been grappling with exactly this problem for years. I'd spend a few hours at a time, and always give up.

I'd have preferred that MS have a built-in (or custom) format for it so that I could still use the data in other calculations, but this is the next best thing.

Many thanks to Scifibum and JohnSinde.

Betelgeuse.