Recognizing Time > 24h

[mferrisi]

Does any one have a good way of handling this? I have the difference between two dates that are then placed in an array. I need 25:55 to show up as 25:55 and not 1:55.

Thanks,

mferrisi

[austenr]

Depending on what you actually have in your cells you might need something like:

[VBA]=REPLACE(TEXT(B1,"0000"),3,0,":")-REPLACE(TEXT(A1,"0000"),3,0,":")
[/VBA]

[RonMcK3]

mferrisi,

Please look at Format > Cells > Number (tab) > Category > Custom

Once there, scrolll down to find h:mm (or h:mm:ss) and change it to [h]:mm ([h]:mm:ss) in the Type field by adding the brackets; you can also add a 2nd 'h', giving [hh], if you always want to see 2-digit number of hours (e.g. 02:31 vs 2:31).

Will this accomplish what you're looking for?

Cheers!

[mferrisi]

I'm looking for something entirely in the code, so things like

[VBA]
i = format(ijk, "[h]:mm ")
[/VBA]


aren't working.

[mohanvijay]

hai try this


VBA:

i=datediff("h",fromdate,tofate) ' returns hour between two dates
j=datediff("n",fromdate,todate) ' returns minute between to dates

onlyminute=j mod 60

myformat=i & ":" & onlyminute

[p45cal]

I'm looking for something entirely in the code, so things like

[vba]
i = format(ijk, "[h]:mm ")
[/vba]

aren't working.

I can't find the equivalent format expression for that anywhere, so maybe use:
[VBA]i=Application.Text(ijk, "[hh]:mm")[/VBA]instead?

[RonMcK3]

mferrisi,

I recorded a macro to see the VBA it creates for doing the time math you are asking about. I attached the file, however, for simplicity, here is the macro:[vba]Sub Macro1()
'
' Macro1 Macro
' Macro recorded 8/23/2010 by Ron
'

'
ActiveCell.FormulaR1C1 = "8/20/2010 13:30"
Range("B2:B4").Select
Range("B3").Activate
ActiveCell.FormulaR1C1 = "8/23/2010 11:29"
Range("B2:B4").Select
Range("B4").Activate
ActiveCell.FormulaR1C1 = "=R[-1]C-R[-2]C"
Range("B4").Select
Selection.NumberFormat = "[h]:mm"
End Sub
[/vba] Try something like the highlighted lines in your code.

Thanks,

[Zack Barresse]

I'm looking for something entirely in the code, so things like

[VBA]
i = format(ijk, "[h]:mm ")
[/VBA]


aren't working.

I wonder what variable type i is as well. Is it a Date type?