VBA placing date format dd/mm/yyyy as mm/dd/yyyy

Question

I have a code that places the current date on a table, but my VBA is placing it wrong

ActiveSheet.Range("K23").Value = Format(Now(), "dd mm yyyy")

But instead of placing 07/03/2023 is placing 03/07/2023, placing month first. What do I need to do for VBA to put as the first date, with days first.

I would love to see a [mcve] on this. I've just tried exactly your code and it's working perfectly for me. — Enigmativity, Mar 07 '23 at 21:03
How do you know whether the date in `W23` is `3-Jul-2023` or `7-Mar-2023`? — Ron Rosenfeld, Mar 08 '23 at 01:45
Instead of putting: 07(dd)/03(mm)/2023(yyyy) as the code says He puts: 03(mm)/07(dd)/2023(yyyy). Removing the "/" fixed mt code though — Cooper, Mar 09 '23 at 12:15
Does this answer your question? [excel date format not same as in vba](https://stackoverflow.com/questions/20414851/excel-date-format-not-same-as-in-vba) — bonCodigo, May 03 '23 at 16:59

BigBen · Accepted Answer · 2023-03-07T21:14:57.377

2

Try:

With ActiveSheet.Range("K23")
   .Value = Date
   .NumberFormat = "dd mm yyyy"
End With

IMO, much better to write an actual date to a cell and then adjust the .NumberFormat, rather than writing a String.

edited Mar 07 '23 at 21:14

answered Mar 07 '23 at 19:26

BigBen

1

I can't get the OP's code to replicate their issue and your code is setting `Value` as a date and then formatting the cell - which is different from what the OP is doing. Maybe the OP is just trying to display a formatted date, but they have handwritten the code instead of recording a macro, so I feel there is some intent to place text in the cell rather than a date value. Something feels odd here. – Enigmativity Mar 07 '23 at 21:09
It worked perfectly, but there is still a doubt about why using "/" in the "dd/mm/yyyy" code, VBA puts the "mm" in front. Instead of putting: 07(dd)/03(mm)/2023(yyyy) He puts: 03(mm)/07(dd)/2023(yyyy) – Cooper Mar 09 '23 at 12:14

1 Answers1