Passing Dates To Excel

PaulSR · Post by **PaulSR** » Thu Mar 27, 2014 12:08 pm

Hi All,

A strange one I've encountered today.

I've got a macro that sources data from a few different sources and then sends it to a single MS Excel spreadsheet where it is then processed. The problem I'm having is with one of the dates where it is being transformed to US format - there are multiple dates being passed but only this one is affected and it comes from a different place to the others (a web page).

I added a MDL> before passing the data to see what Macro Scheduler held and it was in the correct format, I've tried formatting the cell and all that kind of stuff but to no avail - as long as the date is valid when flipped it'll do it (so for example 13/4/2014 will stay that way but 2/4/2014 will become 4/2/2014)

My immediate thought is to use MS to break the date down into its DD MM and YYYY components, pass them individually to MS Excel and then rebuild the date in there but I was wondering if anybody has experienced this before and if they found a common cause or solution ?

Thanks in advance !

Paul.

Post by **Marcus Tettmar** » Thu Mar 27, 2014 1:32 pm

Interesting. Same thing happening here regardless of what the format settings for the column are.

And searching the web yields numerous accounts of similar issues where dates are being programmatically sent into Excel.

You could do as you suggest and try formatting the date, or you could treat is a text and just put a ' in front of it. That would stop Excel doing anything to it as it would go in as raw text. You could then always have an extra column in Excel which contains a formula which converts this into a real date value.

Let>sdate='02/04/2014
XLSetCell>xlH,Sheet1,1,6,sdate,res

Post by **Marcus Tettmar** » Thu Mar 27, 2014 1:52 pm

Actually this works and is probably better:

Let>sdate==DATEVALUE("2/4/2014")
XLSetCell>xlH,Sheet1,1,6,sdate

Make sure the column is formatted to UK dates and .. Well it works for me.

PaulSR · Post by **PaulSR** » Thu Mar 27, 2014 2:21 pm

Thanks Marcus - Interestingly I did as I said and split the date out then passed it to three cells in Excel, built it back into a date and put it into the cell as a whole - and it flipped it again !!!!

Sorted it by not passing the date to a cell once built and instead just built it in memory and stored it as a variable.

I will definitely have a play with your suggestions though !