[VBA] Date/Time in cell in wrong format despite FORMAT() function.

[NessPJ]

Hello all,

I'm using the following code to put a Date/Time in some cells:

Public Const TargetSht As String = "Info"

Sheets(TargetSht).Range("J10").Value = Format(Now, "DD-MM-YY HH:MM")

For yesterday, the result of this code is: 04-12-18 12:00 while according to our standards (I'm from the Netherlands),
i expected it to be: 12-04-18 12:00.

If i run the code again right now, it will show: 13-04-18 12:00.
The day and month values are switched, showing the format i would expect.

How can i correct the notation for the first 12 days of every month? Why does this happen?

 

[joeu2004]

Try:

With Sheets(TargetSht).Range("J10")
.NumberFormat= "DD-MM-YY HH:MM"
.Value = Format(Now, "DD-MM-YY HH:MM")
End With

or

Sheets(TargetSht).Range("J10").Value = Chr(39) & Format(Now, "DD-MM-YY HH:MM")

The latter prefixes the text with an apostrophe (single-quote), and the result in the Excel cell is text, not numeric time. I wrote Chr(39) instead of "'" (double-quote apostrophe double-quote) to make it clearer.

Finally, try your original statement, but use Value2 instead of Value. I don't think it will fix the problem. But I don't time to try it myself.

When we store into an Excel cell (at least, using the Value property), Excel interprets the value just as if we had typed it manually.

 

[Fluff]

try

With Sheets(TargetSht).Range("J10")
   .Value = Now
   .NumberFormat = "DD-MM-YY HH:MM"
End With

 

[Norie]

The problem is more likely because of the Format function.

Sheets(TargetSht).Range("J10").Value = Now()

 

[joeu2004]

PS....

When we store into an Excel cell (at least, using the Value property), Excel interprets the value just as if we had typed it manually

Try:

With Sheets(TargetSht).Range("J10")
.NumberFormat= "DD-MM-YY HH:MM"
.Value = Now
End With

I just read the part where you say that 13-4-18 works, but 12-4-18 does not.

I suspect that one is interpreted as text, not as numeric time. Use the ISTEXT function to confirm.

By changing the cell format and storing numeric time (Now instead of Format), that should give you consistent results.

 

[NessPJ]

Thanks guys!

Both solutions seem to work, though i prefer Fluff's solution since my date will stay an actual date datatype.
[Edit]
Yeah, your second post shows this too. Thanks!

 

[Fluff]

Glad we could help & thanks for the feedback

 

[joeu2004]

@NessPJ.... Generally, it is better to set the NumberFormat before storing to the Value (or Value2) property, just in case the initial cell format is Text.

 

[MrGadget2000]

Try:

Sheets(TargetSht).Range("J10").Value = Chr(39) & Format(Now, "DD-MM-YY HH:MM")

Just came across this and this suggestion helped me out. Adding this just to further assist anyone else who comes across this.
Note I am using Office 365.

Rather annoyingly I had the variable formatted to the right format, when debug printed it looked perfect, but when written to the sheet the month and date swapped around but only if the rate was 12 or less. so;
4/10/2021 09:00:00 am
would become
10/04/2021 09:00:00 am

Adding this as text still allowed me to utilise the saved cell info to create an outlook appointment without issue however.

A little snippet of the failing code (where times and startdate are variables)

 times = Format(times, "hh:mm:ss am/pm")
    startdate = Format(startdate, "d/m/yyyy")
    Sheet2.Range("P1").Offset(j) = startdate & " " & times

I swapped out that last line for;

Sheet2.Range("P1").Offset(j) = Chr(39) & Format(startdate & " " & times, "dd/mm/yyyy hh:mm am/pm")

and problem solved - thanks!