XLSET Date Issue (American Format)

[CyberCitizen]

Having an issue where the date that is being entered into Excel appears in American format, no mater what format I select in the workbook, although outputting to a MDL it appears correct.

Code: Select all

GetDate>vTodaysDate
DateAdd>vTodaysDate,D,5,vSundayDate

XLOpen>C:\Workspace\USG Timesheet.xls,1,xlBook
SetFocus>Excel*
XLSetCell>xlBook,Time Sheet,16,4,9.00,res
XLSetCell>xlBook,Time Sheet,16,5,0.50,res
XLSetCell>xlBook,Time Sheet,16,6,17.00,res

XLSetCell>xlBook,Time Sheet,17,4,9.00,res
XLSetCell>xlBook,Time Sheet,17,5,0.50,res
XLSetCell>xlBook,Time Sheet,17,6,17.00,res

XLSetCell>xlBook,Time Sheet,11,3,%vSundayDate%,res

[hagchr]

Hi, probably something relating to the regional settings? For me this would usually work:

Code: Select all

GetDate>vTodaysDate
DateAdd>vTodaysDate,D,5,vSundayDate

DatePart>vSundayDate,Y,yyyy
DatePart>vSundayDate,M,mm
DatePart>vSundayDate,D,dd
Let>date=%yyyy%-%mm%-%dd%

XLOpen>C:\Workspace\USG Timesheet.xls,1,xlBook

SetFocus>Excel*

XLSetCell>xlBook,Time Sheet,16,4,9.00,res
XLSetCell>xlBook,Time Sheet,16,5,0.50,res
XLSetCell>xlBook,Time Sheet,16,6,17.00,res
XLSetCell>xlBook,Time Sheet,17,4,9.00,res
XLSetCell>xlBook,Time Sheet,17,5,0.50,res
XLSetCell>xlBook,Time Sheet,17,6,17.00,res

XLSetCell>xlBook,Time Sheet,11,3,%date%,res

[CyberCitizen]

Yeah thats how it appears (american format).

I need it in Australian format (Day / Month / Year).

Which it is in, until it posts in excel using the excel command.

[hagchr]

I originally had the same problem as you, but with the code above, if I choose eg dd/mm/yy as format in excel then that is the way the data is presented after the script has run. Have you changed the cell format in excel since you ran it before? Otherwise you possibly still have the American format in there.

[CyberCitizen]

I have changed that multiple times should show it in the gift, that is why I am not sure where it's going wrong.