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

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.

--
Rick (MVP - Excel)


"Steve" wrote in message
...
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

Steve,

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

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

Mike

"Steve" wrote:

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

Thanks Mike

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

Steve

"Steve" wrote:

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