date string to DATE type

[Kurt Jakobsen]

Hello,

I want to store a date string, e.g "07.05.2007" in excel as a date type. Have tried storing it as cstring, but then it does not apear as the date type in excel. I suspect that it is possible to store it as a DATE type, convert it into a variant using VARIANT CVIFUNC CA_VariantDate(DATE val) and store it in excel. But I have not found a way to convert a cstring into a DATE type.

Any help on this issue is most appreciated.

Best regards

Kurt

[rjohnson]

hi,

this may be tangential or oversimplifying your problem, but here it is in case it helps:

i used to do a lot with Excel and Perl, and I made things way too complicated because i kept attempting to manually convert dates to Excel's bizarre proprietary format (number of days since Jan 1, 1900, IIRC).  

I later realized it's much easier if you just put it in a date format that excel already recognizes.

Excel doesn't recognize the dot delimited format you gave an example of (dd.mm.yyyy).  

and it only recognizes "day-first, month-second" if you use month abbreviations (dd-Mon-yyyy, etc.)

so if you just reformat your date string to something Excel can figure out, you can probably skip any other manipulations.   for your example, it might be easiest to change the string to read "05-07-2007".   or even "05/07/2007".   something like that.

anyhow, it works for me.

-RJ

Message Edited by rjohnson on 05-08-2007 05:07 PM

[rjohnson]

double post

Message Edited by rjohnson on 05-08-2007 05:07 PM

[Kurt Jakobsen]

Thanks RJ! That did the trick!

best regards

Kurt

[rjohnson]

 youre welcome.  glad it helped.

one thing, though...   Excel /can/ understand dates in other formats (such as, dd.mm.yyyy),  but you will have to set up a custom field in the "Format Cells" option in the spreadsheet.

where Category = "custom"

and Type is a format string such as "dd.mm.yyyy hh:mm;@"

it's easy enough to do, but requires an extra step.  if you want the Excel generation to be fully automated you'll have to look into how to change the custom category.   In Perl it was accomplished through OLE functions; I'm sure theres a similar way in CVI.