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?


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")
| improve this answer | |
  • 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 Oct 31 '14 at 8:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.