Strange behavior defining the value of a cell as date

[eduzs]

My code have this line which is to insert current date in a cell:

Application.ScreenUpdating = False
with sheets(1)
.Range("I" & LstRow) = Day(Now) & "/" & Month(Now()) & "/" & Year(Now())
            .Range("I2:K2").Copy
            .Range("I" & LstRow & ":K" & LstRow).PasteSpecial xlPasteFormats
Application.ScreenUpdating = True
end with

The two last lines is only to copy format configuration from rows 2 to the line being added.

Supose that today is 8-may-2019, the problem is that sometimes I got 5/8/2019 and sometimes 8/5/2019 I didn't find a cause to this.

Ps1.: This is not a problem with date mask, cause the formulas which calculate date dif returns completely wrong number of days.
Ps2: In my area the default date format is dd/mm/yyyy.

Any ideas?

Thanks.

 

[Scott T]

Instead of constructing the date try just put today's date in I assume the format you are pasting is a date format.

Application.ScreenUpdating = FalseWith Sheets(1)
.Range("I" & LstRow) = Date
            .Range("I2:K2").Copy
            .Range("I" & LstRow & ":K" & LstRow).PasteSpecial xlPasteFormats
Application.ScreenUpdating = True
End With

 

[eduzs]

I will try tomorrow, thanks! Then I will update here.

 

[eduzs]

It did not worked.
Maybe be a bug (sometimes return correct date, sometimes wrong date).
I also tried =now(), datevalue(day(date)&"/"&month(date)&"/"&year(Date)), etc.
Lastly I will try Cdate (Date) date (now ()).
And also will try to pass the date indirect cvdate(cdbl(date))
Thanks

 

[Scott T]

I always get the correct date. I would say there is something else going on and the problem is not with the code.

 

[eduzs]

I did a google search and found that we should avoid writing dates direct to the cells (probably a excel bug).
So, I changed the code for this that is working fine:

.Range("I" & LstRow) = Cdbl(Date) 
.Range("I" & LstRow).NumberFormat = "d/m/yyyy"

 

[MARK858]

Why Cdbl? a date is a whole number so even if there was an issue writing dates direct to the cells (there isn't as far as I am aware doing if you doing it directly with Date, if you do manipulation on it in VBA then there sometimes is as VBA only recognizes US dates, this you were doing in your original code) you would only need Clng.

Now() would need Cdbl as it includes the time which is a decimal part.

 

[eduzs]

Thanks!
Yes my region is (d-m-yyyy), so my date is not write in the EN-US standards (m-d-yyyy), I suppose this maybe the origin of the problem with sometimes got 5-8-2019 and in others times 8-5-2019.
I do not know if there is anything else causing this, for me, apparently, the data in the cells are always the same type.
My workbook contains two worksheets, the first with tasks, when the task is finished my macro transfers the line to the second worksheet (which is not active, is only displayed when I need to consult) and writes in the last column the date of task end and the total number of days the task took.
I do not need time, only day, month and year, so Date looks working fine.
With Clng the time will be discarded (only date is passed)?
Until now it's working fine.
I will update here in the case of problems again.

Why Cdbl? a date is a whole number so even if there was an issue writing dates direct to the cells (there isn't as far as I am aware doing if you doing it directly with Date, if you do manipulation on it in VBA then there sometimes is as VBA only recognizes US dates, this you were doing in your original code) you would only need Clng.

Now() would need Cdbl as it includes the time which is a decimal part.