Best IT Recipes

Excel Macro: How to get a timestamp in the format "yyyy-MM-dd hh: mm: ss"?

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

Shows the timestamp in the format "dd-MM-yyyy hh: mm: ss".

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

+69
vba excel-vba excel
Mar 28 '11 at 9:32 a.m.
source share
10 answers

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

+96
Mar 28 '11 at 9:37
source share

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

They can be formatted as a string to display in any valid date format using the Format function, as indicated in other answers

 Format(DateTime.Now, "yyyy-MM-dd hh:mm:ss")
+40
Mar 28 2018-11-11T00:
source share
 Format(Now(), "yyyy-MM-dd hh:mm:ss")
+6
Mar 28 '11 at 9:39
source share

If some users of the code have different language settings, the format may not work. Thus, I use the following code, which gives a timestamp in the 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
+3
Sep 23 '15 at 7:19
source share

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"
+2
May 6 '16 at 12:39
source share

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

+1
Nov 14 '13 at 16:17
source share

The timestamp when saving the path to the book, " : " must be changed. I used " : " → " . ", Which means that I need to add the extension back to " xlsx ".

 wb(x).SaveAs ThisWorkbook.Path & "\" & unique(x) & " - " & Format(Now(), "mm-dd-yy, hh.mm.ss") & ".xlsx"
0
Oct 27 '18 at 12:14
source share

Use the "Format" function.

 Format(Date, "yyyy-mm-dd hh:MM:ss")
-one
Mar 28 2018-11-11T00:
source share

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

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

Example, my sheet is called Sources

 Sheets("Sources").Range("D3") = Now
-one
Sep 28 '18 at 15:39
source share
 Try with :-Format(CURDATE(),"yyyy-mm-dd hh:MM:ss")
-2
Oct 27 '18 at 12:22
source share


More articles:


All Articles