Excel shows wrong date format in cell

[syzer]

Dear guys,

I would like to input through the VBA in the cell the current date with format "dd/mm/yyyy", however finally in the cell shows up in changed format ("mm/dd/yyyy") and I dont know the reason why and how can I solve it.

As you can see the variable is defined as Date and until the exact row the variable value is proper, only after writing in the cell is changing.

Refer the highlighted rows.

Thank you in advance!

 

[KOKOSEK]

Try:

NowDate = CDate("01/11/2024")

 

[dmt32]

As you can see the variable is defined as Date

As it is nearly pantomime season the reply from programmers would be, “Oh No Its Not!

In your post, Only the LogDate variable is Date datatype NowDate is Variant data type.

When declaring variables each variable requires its data type to be specified even when it is on the same line otherwise it will be its default type of variant.

Your variables should be declared like this

   Dim NowDate As Date, LogDate As Date

Also, by using Format in the manner shown, the date will be a string.

To resolve your issue you can try following & see if works for you

NowDate = Now()


With ActiveSheet.Range("K" & RowNo)

.Value = NowDate

.NumberFormat = "dd/mm/yyyy"

End With

Dave

 

[syzer]

Try:

NowDate = CDate("01/11/2024")

Thank you for your support, it's solved my problem.

 

[syzer]

As it is nearly pantomime season the reply from programmers would be, “Oh No Its Not!

In your post, Only the LogDate variable is Date datatype NowDate is Variant data type.

When declaring variables each variable requires its data type to be specified even when it is on the same line otherwise it will be its default type of variant.

Your variables should be declared like this

   Dim NowDate As Date, LogDate As Date

Also, by using Format in the manner shown, the date will be a string.

To resolve your issue you can try following & see if works for you

NowDate = Now()


With ActiveSheet.Range("K" & RowNo)

.Value = NowDate

.NumberFormat = "dd/mm/yyyy"

End With

Dave

Thank you for your proper explanation and your support. It's helped.

 

[dmt32]

You are welcome glad we were able to help resolve your issue & appreciate your feedback

Dave