To populate a cell with the current timestamp, the Now function is typically used in conjunction with Format. For instance, if the current moment is July 24th at 16:04:29, using Format(Now, "mmdhhs") will yield the string 07241629.

You can customize the output string based on your specific display requirements.

Displaying Full Timestamp The following macro writes the exact date and time to the currently selected cell:

Sub WriteFullTimestamp()
    Selection.Value = Format(Now, "yyyy年mm月dd日 hh时mm分ss秒")
End Sub

Displaying Only the Date If you only need the calendar date without the time component, use the Date function:

Sub InsertDateOnly()
    ActiveCell.Value = Format(Date, "yyyy年mm月dd日")
End Sub

Removing Leading Zeros To display the date without leading zeros (e.g., 2023年1月5日 instead of 2023年01月05日), adjust the format string by using single placeholders:

Sub InsertDateNoLeadingZero()
    ActiveCell.Value = Format(Date, "yyyy年m月d日")
End Sub

The same logic applies to time portions: (e.g., yyyy年m月d日 h时m分s秒).

Practical Formatting Examples

The Format function offers versatility in how data is represented. Note that standard date and time separators (like / or :) may vary based on the system's regional settings (Locale ID). The examples below assume a US English standard context.

Dim dtSample As Date
Dim tmSample As Date
Dim strResult As String

' Assign specific values for demonstration
dtSample = #1/27/1993#
tmSample = #5:04:23 PM#

' Using system-defined long formats
strResult = Format(Time, "Long Time")
strResult = Format(Date, "Long Date")

' Custom time formats
strResult = Format(tmSample, "h:m:s")            ' Returns "17:4:23"
strResult = Format(tmSample, "hh:mm:ss AMPM")     ' Returns "05:04:23 PM"

' Custom date formats
strResult = Format(dtSample, "dddd, mmm d yyyy")  ' Returns "Wednesday, Jan 27 1993"

' Formatting numbers
strResult = Format(23)                           ' Returns "23"
strResult = Format(5459.4, "##,##0.00")          ' Returns "5,459.40"
strResult = Format(334.9, "###0.00")             ' Returns "334.90"
strResult = Format(5, "0.00%")                   ' Returns "500.00%"

' Formatting text case
strResult = Format("HELLO", "<")                ' Returns "hello"
strResult = Format("This is it", ">")            ' Returns "THIS IS IT"

For numeric values, the Round function is useful for controlling decimal precision. For string manipulation, functions like Left and Right allow you to extract specific portions of the text.