86

I am using DateTime.Now in my Excel Macro to show the current timestamp.

It shows timestamp in "dd-MM-yyyy hh:mm:ss" format.

Instead, how can I get the timestamp in "yyyy-MM-dd hh:mm:ss" format?

pgSystemTester
  • 8,979
  • 2
  • 23
  • 49
Parth Bhatt
  • 19,381
  • 28
  • 133
  • 216

10 Answers10

125

Try with: format(now(), "yyyy-MM-dd hh:mm:ss")

Mohamed Saligh
  • 12,029
  • 19
  • 65
  • 84
43

DateTime.Now returns a value of data type Date. Date variables display dates according to the short date format and time format set on your computer.

They may be formatted as a string for display in any valid date format by the Format function as mentioned in aother answers

Format(DateTime.Now, "yyyy-MM-dd hh:mm:ss")
chris neilsen
  • 52,446
  • 10
  • 84
  • 123
7
Format(Now(), "yyyy-MM-dd hh:mm:ss")
Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
4

If some users of the code have different language settings format might not work. Thus I use the following code that gives the time stamp in format "yyymmdd hhMMss" regardless of language.

Function TimeStamp()
Dim iNow
Dim d(1 To 6)
Dim i As Integer


iNow = Now
d(1) = Year(iNow)
d(2) = Month(iNow)
d(3) = Day(iNow)
d(4) = Hour(iNow)
d(5) = Minute(iNow)
d(6) = Second(iNow)

For i = 1 To 6
    If d(i) < 10 Then TimeStamp = TimeStamp & "0"
    TimeStamp = TimeStamp & d(i)
    If i = 3 Then TimeStamp = TimeStamp & " "
Next i

End Function
Kaisa
  • 81
  • 4
3

this worked best for me:

        Cells(partcount + 5, "N").Value = Date + Time
        Cells(partcount + 5, "N").NumberFormat = "mm/dd/yy hh:mm:ss AM/PM"
user6300304
  • 39
  • 1
1

Copy and paste this format yyyy-mm-dd hh:MM:ss in format cells by clicking customs category under Type

Rafiq
  • 19
  • 1
0

Timestamp in saving workbook path, the ":" needs to be changed. I used ":" -> "." which implies that I need to add the extension back "xlsx".

wb(x).SaveAs ThisWorkbook.Path & "\" & unique(x) & " - " & Format(Now(), "mm-dd-yy, hh.mm.ss") & ".xlsx"
Wizhi
  • 6,424
  • 4
  • 25
  • 47
0

You can use the Format function below:

$(get-date -f yyyyMMddhhmmss)
Jeremias Tivane
  • 1
  • 1
-1

It can work as easy as this, choose the location you want, in this case I choose D3

Sheets("Put your Sheet's name here").Range("D3") = Now

Example, my sheet is called Sources

Sheets("Sources").Range("D3") = Now
pushkin
  • 9,575
  • 15
  • 51
  • 95
Flori
  • 1
-2

Use the Format function.

Format(Date, "yyyy-mm-dd hh:MM:ss")
Arnoud Kooi
  • 1,588
  • 4
  • 17
  • 25
  • 1
    Don't work! `Date` contain only the calendar date but the hour, minutes and seconds are fixed to 0:00:00 ! You have to use `Now()` instead.. – A.Sommerh May 20 '14 at 14:52