When I try to display the date in Excel in the format dd/mm/yyyy hh , I get it, the date looks as expected:

10/20/2016 17

10/20/2016 18

In the case above, I display the date and time as follows:

 nRowDate = 1 For nDay = 20 To 21 For nHour = 0 To 23 If (nDay < 10) Then Str = "0" + CStr(nDay) + "/" + MonthNumber + "/" + Year + " " Else Str = CStr(nDay) + "/" + MonthNumber + "/" + Year + " " End If If (nHour < 10) Then Str = Str + "0" + CStr(nHour) Else Str = Str + CStr(nHour) End If Date_Array(nRowDate) = Str nRowDate = nRowDate + 1 Cells(nRowDate, 1).Value = Format(Str, "dd/mm/yyyy hh") Next nHour Next nDay

But I can't get the date in dd/mm/yyyy hh:mm:ss format, the output looks like this:

10/20/2016 00:00:00

10/20/2016 00:00:10

Here is the code:

 nRowDate = 1 For nDay = 20 To 21 For nHour = 0 To 23 For nMinutes = 0 To 60 For nSec = 0 To 5 If (nDay < 10) Then Str = "0" + CStr(nDay) + "/" + MonthNumber + "/" + Year + " " Else Str = CStr(nDay) + "/" + MonthNumber + "/" + Year + " " End If If (nHour < 10) Then Str = Str + "0" + CStr(nHour) Else Str = Str + CStr(nHour) End If If (nMinutes < 10) Then Str = Str + ":0" + CStr(nMinutes) Else Str = Str + ":" + CStr(nMinutes) End If If (nSec = 0) Then Str = Str + ":00" Else Str = Str + ":0" + CStr(nSec * 10) End If Date_Array(nRowDate) = Str nRowDate = nRowDate + 1 Cells(nRowDate, 1).Value = Format(Str, "dd/mm/yyyy hh:mm:ss") Next nSec Next nMinutes Next nHour Next nDay

Where did I go wrong? ..

  • Everywhere .. Date must be assigned with type Date, not String. A change the output form through the format of cells, and not through the formatting of the line. - Qwertiy ♦
  • @Qwertiy, thanks for the comment! Can you write an example in response? - Ksenia
  • Please describe why such nesting cycles? What do you want to get? - vikttur
  • @vikttur, it seems obvious - days, hours, minutes, seconds. What is wrong with nesting? - Qwertiy ♦
  • I am about the task itself. Forget about VBA for a while) The main task in what? - vikttur

3 answers 3

  1. The date must be assigned with type Date, not String.
  2. Output format to change through the format of cells, and not through the formatting of the line.

You can do something like this:

 Sub Fill() Dim D As Integer, H As Integer, M As Integer, S As Integer Dim I As Integer I = 0 For D = 20 To 21 For H = 0 To 23 For M = 0 To 59 For S = 0 To 50 Step 10 I = I + 1 With ActiveSheet.Cells(I, 1) .Value = DateAdd("s", (CLng(H) * 60 + M) * 60 + S, DateSerial(2016, 10, D)) .NumberFormat = "dd\/mm\/yyyy hh:mm:ss" End With Next S Next M Next H Next D End Sub

I note that the format string uses dd\/mm\/yyyy , not dd/mm/yyyy - in the case of unshielded slashes, the separator will be taken from the regional settings.

But desirable:

  1. Preset format for range
  2. Somehow it is more normal to create a date - it seems I have become too smart.
  • Thank you very much! I just added :mm:ss to the format of cells) - Ksenia
  • @Ksenia, the question was without them, so I thought that was the way. - Qwertiy ♦

Set the data format for the cell itself.

 Cells(1, 1).NumberFormat = "dd\/mm\/yyyy hh:mm:ss" Cells(nRowDate, 1).Date = Date()
  • 2
    The format is incorrect, so that there are exactly slashes, you need to "dd\/mm\/yyyy hh" , otherwise it will take it from the regional settings. - Qwertiy ♦
  • @Qwertiy thanks for the clarification - Anton Shchyrov

Output to the cell with the necessary formatting:

 Cells(1, 1).Value = Format(Now, "dd/mm/yy yy:mm:ss") Cells(1, 1).Value = Format(Now, "dd\/mm\/yy yy:mm:ss") ------------------------------------------ ' вывести время в формате dd/mm/yyyy hh:mm:ss с заданной периодичностью Sub DateTime10() Dim dS As Date, dF As Date Dim dStep As Double Dim i As Long, n As Long With ActiveSheet dS = .Cells(1, 2).Value ' старт dF = .Cells(2, 2).Value ' финиш dStep = .Cells(3, 2).Value ' шаг, к-во секунд End With n = Int((dF - dS) * 86400 / dStep) + 1 ' к-во шагов dStep = dStep / 86400 ' в секундах (время) dS = dS - dStep ' на шаг назад ReDim ArrD(1 To n, 1 To 1) ' размерность массива For i = 1 To n ArrD(i, 1) = dS + dStep * i Next i With ActiveSheet .Columns(4).ClearContents ' чистим перед загрузкой .Cells(1, 4).Resize(n, 1).Value = ArrD ' результат на лист End With End Sub

enter image description here

To display the date in a different form - the same format as proposed in the code, but for the cells:

 ДД\/ММ\/ГГГГ чч:мм:сс