Problem with Date Format

[tekniks]

I have the following code which updates/copies the cell containing date with format "09-SEP-2003" from another sheet:
If cstsDoss_val <> Doss_val Then
ws1.Cells(R, 7).Replace _
What:=Doss_val, Replacement:=cstsDoss_val, _
SearchOrder:=xlByColumns, MatchCase:=False
Else
Doss_val = Doss_val
End If

Somehow after replacing or updating the date value the date shows as &quot;04-SEP-2003&quot; but somehow in the formula bar it shows as 4/9/2003 and when I compare the worksheet cells it shows that these two values are different and again updates with the same thing putting an extra load on the update function for all the values.

Any thoughts on how to keep the value as &quot;04-SEP-2003&quot; even in the formula bar although putting Format(ws1.cells(R,5).Value, &quot;dd-mmm-yyyy&quot
didn't help.

Thanks in advance.

Tek

 

[RoyVidar]

In my version (XP) i'll use:

ws1.cells(R,5).NumberFormat=&quot;dd-mmm-yyyy&quot;

HTH Roy-Vidar

BTW - in your format-statement, you refer to 'ws1.cells(R,5)' and previou you refer to 'ws1.cells(R,7)', it couldn't be as simple as wrong reference?

 

[tekniks]

Roy,

The reference is correct as it was just a typo.

Tried your solution of:
ws1.Cells(R, 5).NumberFormat = &quot;dd-mmm-yyyy&quot;

But it didn't help.

Still shows &quot;04/09/2003&quot; in the address bar.

Anybody else has any ideas...

TEKS

 

[RoyVidar]

Sometimes one try to be both smart and quick, but end up being neither;-) Sorry, I didn't read you post thoroughly enough. I have one additional thought though:

I browsed thru the help files (XP), found the topic &quot;Troubleshoot formatting worksheets&quot; with subtopic &quot;The number in a worksheet cell isn't the same as the number in the formula bar&quot;; &quot;The number format applied to a cell determines the way Microsoft Excel displays a number in that cell on the worksheet. The format does not affect the cell value used in calculations, which is displayed in the formula bar when the cell is active&quot;.

My guess is that when the formula bar displays &quot;09-SEP-2003&quot;, it might be displaying the text &quot;09-SEP-2003&quot;, and not the date (and therefore a new update). The formula bar ususally displays dates according to the regional settings you use, so when &quot;04/09/2003&quot; is displayed, it might be considered an assurance that it's actually a date in the cell, and not text.

You might check that with the TypeName function, which will return the datatype Excel consider a cell to contain.

Debug.Print TypeName(ws1.Cells(R, 5))

Most datefunctions in both Excel and VBA are pretty ignorant of what datatype you &quot;put in&quot;, as long as it might be considered a date, the formula bar displays text as text, and dates according to regional settings.

Using those thoughts, why doesn't the formula bar show for instance 37891 in stead of 27.09.2003;-)

Roy-Vidar