Excel VBA Date format: Hyphen as separator gives wierd substitution result

Excel 2007.

I want a macro to put today's date in the current cell formatted as dd-mmm-yy, e.g. 30-Oct-14.

The code I have is simple :

'ActiveCell.Value = Format(Now(), "dd-mmm-yy")'

For some reason this returns "30 Oct 14" with spaces instead of hyphens.

Similar formats work correctly. e.g

dd-mm-yy gives 30-10-14,
dd~mmm~yy gives 30~Oct~14

The cells actual value is set to "10 30 2014"

Can anyone give me a simple workaround, and if possible, explain why this is happening?

Malcolm Morris

Posted 2014-10-30T06:25:49.047

Reputation: 1

See https://stackoverflow.com/questions/19781234/how-to-concatenate-datemm-dd-yyyy-with-time-hhmmss-using-vba/19784256#19784256

– rakslice – 2014-10-30T06:43:11.880

Answers

Excel is automatically converting that text you inputting with the macro into a date (and then applying a date format. To force excel to view it as text, prepend a single quote to the date string:

ActiveCell.Value = "'" + Format(Now(), "dd-mmm-yy")

Alternatively, set the format of the cell to text type first:

ActiveCell.NumberFormat = "@"
ActiveCell.Value = Format(Now(), "dd-mmm-yy")

Andrew Cave

Posted 2014-10-30T06:25:49.047

Reputation: 11

Thanks. Both options display correctly, though the second, without the single quote, is what I need. I'd upvote/mark as solved if I had the reputation... – Malcolm Morris – 2014-10-31T08:31:30.230