Entering Date Correctly in Excel

[winstein]

(Topic previously found in the Beginners section)

When I used the XLSetCell function to enter a date, if that Day and Month are both 12 or less, the system has a tendency to interpret my dates as Month/Day/Year format instead of the Day/Month/Year format. This is in spite of cell format being in the D/M/Y format. Here's a sample code (make sure the first cell in Excel is set to D/M/Y format first):

Code: Select all

XLOpen>[[Insert Excel file here]],1,xlBook
XLSetCell>XLBook,Sheet1,1,1,10/2/2016,Result_Var

That seems to be a glitch here, as it will, without fail, convert the date to M/D/Y format when both Day and Month are 12 or less each.

Thanks for reading.

[Marcus Tettmar]

Hi,

XLSetCell is using ADO/COM to send the value and I think when you use an ambiguous format ADO assumes you are sending it in US format. E.g. it thinks by 02/06/2016 you mean 6th Feb. It makes sense when you think about it - it can't possibly know what you mean so has to fall back on something.

So what you need to do is use a less ambiguous format when sending the date. E.g.:

XLSetCell>xlH,Sheet1,4,7,2016-06-02,res

When you do that you will see that it ends up in Excel as 2nd June which is correct, regardless of what date format the cell is set to in Excel.

So use YYYY-MM-DD when you set the cell. It won't appear like that unless you are using that format. It will appear in whatever format you have those cells set to. You should find it will cope better, because it understands what you are sending it is 2nd June 2016.