Interpret Cell as it is

[AlexanderBB]

This gets me every time.. looking at the worksheet Cell I see 2:15

But reading Cells(row, Col) in VBA results in 0.09375. I think this is because the cell is formatted to suit a different data type.

Is there a solid way to get "2:15" ? I tried CStr but no change.

 

[sheetspread]

Here's 2 ways:

Excel 2010
	A	B
1	0.09375	2:15
2		2:15
Sheet1

Cell Formulas
Range	Formula
B1	=HOUR(A1)&":"&MINUTE(A1)
B2	=TEXT(A1,"h:mm")

As with all Excel dates, .09375 is the number of days.

 

[AlexanderBB]

Yes, that works but how would I get that result in VBA ?

 

[RoryA]

Cells(row, Col).Text

will return the displayed content of the cell as a string.

 

[AlexanderBB]

Thanks very much Rory, I never knew that and it'll be very useful.
Resorted to using Range("F:F").Number Format = "@" but your suggestion is preferred.