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?
9 Answers
Try with: format(now(), "yyyy-MM-dd hh:mm:ss")
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") Format(Now(), "yyyy-MM-dd hh:mm:ss") 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 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" Copy and paste this format yyyy-mm-dd hh:MM:ss in format cells by clicking customs category under Type
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" 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 Use the Format function.
Format(Date, "yyyy-mm-dd hh:MM:ss") 
