Printing Excel Dates (Format dd/mm/yy)
 

Could you do PostingDate.NumberFormat="dd/mm/yy" after PostingDate =
..Cells(RSRow, 5)?

"john pritchard" wrote in message
...
Hi,

All I really want to do is read a cell and write its
contents. The contents happen to be a date in UK format
i.e. 31/07/03 means March 31 2003 and 01/08/03 means
August 1 2003. Originally I simply had:-

PostingDate = .Cells(RSRow, 5)

in the appropriate with structure etc with postingdate
defined as a string. Later this was written to another
sheet with :-

Cells(LSRow, 18) = PostingDate


which writes 31/07/03 as expected when that is what is
read BUT 37629 when 01/08/03 is read. I've tried
reformating the Cells in the code with :-

.Cells(RSRow, 5).NumberFormat = "dd/mm/yy"

PostingDate = .Cells(RSRow, 5)

and converting to text with :-

.Cells(RSRow, 5).NumberFormat = "@"

I've also tried using datepart but if I do that 31/07/03
results in year 1931 while 01/08/03 gets 2003. The macro /
VB code is distributed to many PC's and I have no control
over the PC's settings.

The original input file is created with Crystal Reports (I
have no control here either) and the relevant cell is
General format to start with.

As you can imagine inability to read/write 01/08/03 is
embarassing to say the least!

Thanks for any suggestions

JP

Printing Excel Dates (Format dd/mm/yy)
 

How about something I just learned?

Option Explicit
Sub testme()
Dim PostingDate As Double
With Worksheets("sheet1")
PostingDate = .Cells(RSRow, 5).Value2
'''
.Cells(LSRow, 18).Value = PostingDate
.Cells(LSRow, 18).numberformat = "dd/mm/yyyy"
End With

End Sub

The .value2 returns the number in the cell (not date). Kind of like looking at
a date in a cell that's formatted as General.

(and you dropped the leading dot on your cells(lsrow, 18) line. Maybe just in
the newsgroup posting???)


john pritchard wrote:

Hi,

Posting date is a string variable not a cell so I can't do
that. My problem is that Excel is interpreting 01/08/03 as
a date implicitly. I've now realised that if I format the
final output cell as mm/dd/yy I get back to 01/08/03 which
is not what I need for UK dates. I'm told that the final
output (which is an upload to another system) will be
interpreted as an Excel date so 37629 means August 8 2003
while the input meant August 1 2003. My guess is that
31/07/03 didn't get changed because Excel doesn't see this
as a valid date (there beging no month 31). Can I turn
this implicit interpretation off ?

regards

john P.

-----Original Message-----
Could you do PostingDate.NumberFormat="dd/mm/yy" after
PostingDate =
..Cells(RSRow, 5)?

"john pritchard" wrote
in message
...
Hi,

All I really want to do is read a cell and write its
contents. The contents happen to be a date in UK format
i.e. 31/07/03 means March 31 2003 and 01/08/03 means
August 1 2003. Originally I simply had:-

PostingDate = .Cells(RSRow, 5)

in the appropriate with structure etc with postingdate
defined as a string. Later this was written to another
sheet with :-

Cells(LSRow, 18) = PostingDate


which writes 31/07/03 as expected when that is what is
read BUT 37629 when 01/08/03 is read. I've tried
reformating the Cells in the code with :-

.Cells(RSRow, 5).NumberFormat
= "dd/mm/yy"

PostingDate = .Cells(RSRow, 5)

and converting to text with :-

.Cells(RSRow, 5).NumberFormat = "@"

I've also tried using datepart but if I do that 31/07/03
results in year 1931 while 01/08/03 gets 2003. The
macro /
VB code is distributed to many PC's and I have no
control
over the PC's settings.

The original input file is created with Crystal Reports
(I
have no control here either) and the relevant cell is
General format to start with.

As you can imagine inability to read/write 01/08/03 is
embarassing to say the least!

Thanks for any suggestions

JP






.


--

Dave Peterson