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
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
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]
Peace of mind is found in some of the strangest places.
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!
Ron McKenzie
Windermere, FL
(living in the huge shadow of a tiny rodent)
I'm looking for something entirely in the code, so things like
[VBA]
i = format(ijk, "[h]:mm ")
[/VBA]
aren't working.
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
I can't find the equivalent format expression for that anywhere, so maybe use:Originally Posted by mferrisi
[VBA]i=Application.Text(ijk, "[hh]:mm")[/VBA]instead?
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
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,
Ron McKenzie
Windermere, FL
(living in the huge shadow of a tiny rodent)
I wonder what variable type i is as well. Is it a Date type?Originally Posted by mferrisi
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables