21

I am trying to get the Date as a string formatted yyyy-mm-dd.

I have tried various things with strange results:

Dim mydate As String
mydate = Date
mydate = Year(Date)
mydate = Month(Date)
mydate = Day(Date)
  1. The first one gives 11/02/ without the year.

I can try to concatenate the data but:

  1. The second gives the year OK
  2. However the third gives month as 2 instead of 02
  3. Similar for fourth.

Any clarification or an example would be very welcome.

feetwet
  • 3,248
  • 7
  • 46
  • 84
Studix
  • 351
  • 3
  • 4
  • 15

3 Answers3

36

Use the Format function from the VBA.Strings built-in module:

Debug.Print Format(Now, "YYYY-MM-DD")
Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
  • Perfect. Thank you very much ! – Studix Feb 11 '16 at 17:07
  • With this I can output today -1 and with that get yesterday, now I wonder how can I do this with week nr? Debug.Print Format(Now - 1, "WW") dose not work (well it works but doesn't output current week, outputs next week that is why I add -1 but that doesn't do anything. , please if you can help me I appreciate it. – Assarelius Apr 27 '22 at 18:08
  • Sub find_week_number138() Week_Num = WorksheetFunction.WeekNum(Now, vbSunday) Debug.Print "Today’s week number is " & Week_Num 'still giving 18 which is wrong week number End Sub – Assarelius Apr 27 '22 at 19:30
3
Dim sToday As String
sToday = CStr(Date)

That gives sToday value, e.g. "2020-12-31", in the format of my system's date.

Marek M
  • 31
  • 2
1

In some VBA, the Format() function is not available. In this case, you can do it the old fashion:

y = cstr(year(now))
m = right("0" + cstr(month(now)),2)
d = right("0" + cstr(day(now)),2)   
mydate = y + "-"+ m + "-" + d
Philippe
  • 548
  • 8
  • 24