Date Formats

[Steve]

Hi I'm having trouble writing the right date format to a cell.

Currently I have tried

Worksheets("Forecast").Cells(ForecastMaxRowNo, 8).Value = Format(Date,
"Short Date")

Worksheets("Forecast").Cells(ForecastMaxRowNo, 8).Value = Format(Date,
"General Date")

Worksheets(sMonth).Cells(MonthTabMaxRowNo, 8).Value = FormatDateTime(Date,
vbShortDate)

Each time it comes out in American format and my regional setting on the
computer are all set to UK

When I put MSGBOX before it to test it seems to be OK. The Cell format seems
to be OK too.

Any help or suggestions would be appreciated.

Thanks

Steve

 

[Rick Rothstein]

Don't try to set the format using the Value property; rather, assign the
date to the Value property directly and then use the NumberFormat property
to set the format....

With Worksheets("Forecast").Cells(ForecastMaxRowNo, 8)
.Value = Date
.NumberFormat = "dd/mm/yyyy"
End With

Change the format pattern to whatever format you actually want.

 

[Mike H]

Steve,

Doing it like this should make it pick up your regional setting

Worksheets("Forecast").Cells(ForecastMaxRowNo, 8) = Date

Mike

 

[Steve]

Thanks Mike

Seems like I tried everything but the right answer! Really appreciate your
help.

Steve