PDA

View Full Version : Recognizing Time > 24h



mferrisi
08-23-2010, 08:43 AM
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
08-23-2010, 09:17 AM
Depending on what you actually have in your cells you might need something like:

=REPLACE(TEXT(B1,"0000"),3,0,":")-REPLACE(TEXT(A1,"0000"),3,0,":")

RonMcK3
08-23-2010, 10:21 AM
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
08-23-2010, 10:26 AM
I'm looking for something entirely in the code, so things like


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



aren't working.

mohanvijay
08-23-2010, 11:12 AM
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
08-24-2010, 01:37 AM
I'm looking for something entirely in the code, so things like


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


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

RonMcK3
08-25-2010, 06:12 AM
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: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
Try something like the highlighted lines in your code.

Thanks,

Zack Barresse
08-25-2010, 06:51 AM
I'm looking for something entirely in the code, so things like


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



aren't working.
I wonder what variable type i is as well. Is it a Date type?