How to stop VBA code from within excel converting Text to Time?

Question

I have tried formatting the input fields as text but to no avail. When I step through it in the debugger it shows a double number which I presume is date conversion. I just want it to treat it as text and not convert it to a time/date.

Can somebody tell me how to prevent the conversion?

The text field has YY.MM.DD hh:mm:ss in text. I want to remove the seconds so I use:

Select.Value=MID(Select.Value,1,16) 

This does not work. I have put text in the field and it does truncate the last 3 positions.

Answer 1

If you're using VBA, try using Format:

Select.Value=FORMAT(Select.Value,"YY.MM.DD hh:mm")

Answer 2

Try putting a single quote before the text field to force it to be considered as text.

Answer 3

It's not clear what you're trying to achieve with the time value, which affects the solutions proposed. However:

If you specifically want to do it through VBA, you can define a string variable and then pass it the time value:

strTextValue = cstr(format(selection.value,"YY.MM.DD HH:MM"))

You could also do this in the worksheet directly, for example:

=TEXT(range("A1"),"YY.MM.DD HH.MM"))