Date Formats

[scottian]

Im trying to retrieve dates from a system using Attachmate. The date is displayed on the screen as "12/11/09" its copied to a variable and then pasted into a cell in excel. The problem i have is when the date is in the first 9 days of the month, e.g. "09/11/09". For some odd reason the date is pasted to excel as "11/09/09". Reversing the month and the day. Ive tried different cell formats inluding plain text, but no joy. No matter what i try, it does the same thing. Can anyone help?





------------------------------------------------------------------------------
Ambition..........If you dont use it, you wont lose it

 

[SkipVought]

Hi,

When data is entered into an Excel sheet, Excel 'edits' the data and trys to be helpful. Check out faq68-5827.

The problem i have is when the date is in the first 9 days of the month, e.g. "09/11/09".

Here's what happens:
[tt]
You enter the characters 09/11/09 (it's just a string!!!)
Excel assumes that what you want is a date.
Because Bill Gates is one of those colonists across the pond, the ENTRY format is assumed to be mm/dd/yy, so it CONVERTS the string to a DATE, maybe using DateSerial(yyyy,mm,dd) function, resulting in the Date/Time serial value 40067
Then it chooses the default date FORMAT to DISPLAY a recognizable date.
[/tt]
My guess is that you may have other 'dates' that NEVER GET CONVERTED TO REAL DATES. You can easily check by selecting the column containing your dates and changing the format to GENERAL. All REAL DATES will be DISPLAYED as numbers. All STRINGS (not real dates) will appear to look like dates, but are NOT.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[vzachin]

scottian,

try this: format the Excel Date prior to pasting the data