>Hi,
>
>I am having problems outputting certain dates from a cursor to an Excel spreadsheet using OLE automation. The date format setting is SET DATE ITALIAN (ie. dd-mm-yyyy). When the day is greater than 12, the date is correctly output (eg. 24-06-2008) but when the day is less than or equal to 12, the date is output is in mm/dd/yyyy format, ie. 7th January 2005 appears as 01/07/2005 instead of the expected 07-01-2005 (with / instead of - and the days/months reversed). All dates display correctly when I browse the cursor.
>
>The automation code that loads the cell is as follows...
> oWorkSheet.Cells(lnRow,5).value = MyDateField
>
>I have tried the following, all without success...
> SET DATE BRITISH
> SET DATE DMY
> oWorkSheet.Columns(5).NumberFormat = 'dd/mm/yy'
> oWorkSheet.Cells(lnRow,5).value = DTOC(MyDateField)
> oWorkSheet.Cells(lnRow,5).value = TRANSFORM(MyDateField,'@D')
>
>Can anyone tell me how to get all dates into the correct format? Why is Excel trying to convert dates to American format? Any help would be appreciated.
>
>TIA
>Alan


Alan,

I always put a formula in excel for dates - There's never any confusion at all

oWorkSheet.Cells(lnRow,5).value = Date2Excel(MyDateField)

&& next line may not be necessary
oWorkSheet.Cells(lnRow,5).NumberFormat = 'dd/mm/yyyy'


*--------------------------------------------------------------------------
#define YYYYMMDDMASK_EXCEL	'@R ####,##,##'
#define YYYYMMDDMASK_EXCEL_LEN	17

function  Date2Excel(d, EmptyDateExpand)
			
	return iif(	empty(nvl(m.d, {})), ;
					iif(!m.EmptyDateExpand, ;
							'', ;
							space(YYYYMMDDMASK_EXCEL_LEN) ;
					), ;
					'=date(' + transform(dtos(m.d), YYYYMMDDMASK_EXCEL) + ')' ;
				)
endfunc
*--------------------------------------------------------------------------