Hi guys.

Does anybody of you write  XLLs in C++?

How do you deal with dates?
XLOPER12 still does not recognize dates.
Where can I find some useful functions to convert numeric value into date?
Is there a way to recognize if value in cell is formated as date or 
regular numeric value?

I is easy to convert a number into a date.  Excel stores the date as a
number with Jan 1, 1900 equal 1 and ech day afterwards is equalk to 1. 
One hour equal 1/24, 1 minute equals 1/(24*60).  You could write your
own function to convert a number to a date and a date to a number.


-- 
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=160426

[url="http://www.thecodecage.com"]Microsoft Office Help[/url]

joel wrote:
> I is easy to convert a number into a date.  Excel stores the date as a
> number with Jan 1, 1900 equal 1 and ech day afterwards is equalk to 1. 
> One hour equal 1/24, 1 minute equals 1/(24*60).  You could write your
> own function to convert a number to a date and a date to a number.
> 
> 
Theory is beautiful, practice is not.
Leap years, lotus bug  make it more difficult.


Also I have numbers like 40000 and dates like  1/1/2009, and both are 
passed the same way to XLL function. I need to know if there is a way to 
distinguish them. If it is not, I have to rewrite other piece of code, 
which I do not want to.

A date is just a number and is passed between functions as a single
variable type.  Microsoft treat numbers and dates exactly the same
except at the user interface they are displayed differently.  There arre
a few quirks in excel the microsoft screws up the number by trying to be
too smart and looses accuracy of the number.  I sometimes havve to
declare a variable in VBA as a single instead of a date so the built in
VBA functions don't do things I don't want them to do.


You can sue the Wind32 functions to convert your string dates to
number.  See this URL for a list of functions.


'National Language Support Functions (Windows)'
(http://msdn.microsoft.com/en-us/library/dd319081(VS.85).aspx)


I can write VBA functions that can call any of these functions.  In C++
there are usually header funtions in the C++ libraries that can also
call these Win32 DLLs.  I haven't written a lot of C++ code to know
exactly how to use these functions but if figure out which function(s)
you want to use then do a search on the web for sample code.


-- 
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=160426

[url="http://www.thecodecage.com"]Microsoft Office Help[/url]

joel wrote:
> A date is just a number and is passed between functions as a single
> variable type.  Microsoft treat numbers and dates exactly the same
> except at the user interface they are displayed differently.  There arre
> a few quirks in excel the microsoft screws up the number by trying to be
> too smart and looses accuracy of the number.  I sometimes havve to
> declare a variable in VBA as a single instead of a date so the built in
> VBA functions don't do things I don't want them to do.
> 
> 
> You can sue the Wind32 functions to convert your string dates to
> number.  See this URL for a list of functions.
> 
> 
> 'National Language Support Functions (Windows)'
> (http://msdn.microsoft.com/en-us/library/dd319081(VS.85).aspx)
> 
> 
> I can write VBA functions that can call any of these functions.  In C++
> there are usually header funtions in the C++ libraries that can also
> call these Win32 DLLs.  I haven't written a lot of C++ code to know
> exactly how to use these functions but if figure out which function(s)
> you want to use then do a search on the web for sample code.
> 
> 

1.Excel does not follow standard which is used in VBA, so VBA date and 
Excel date are not the same.

2. I do not have strings. I have a number: 42345. And I need to know 
year, month and day.

3. I also need to know if 42345 was entered by user as 42345 or as a 
date which is represented by that number.

#2 is easy, but requires a little bit of thinking :)
I expected, that maybe Microsoft did that thinking for me.

#3 is not easy and I do not know an answer.

#1)  The storage of the date in a wroksheet and VBA are identical.  Some
of the mathematics are a little different.  VBA performs some strange
trunicating and rounding of dates that on't happen in the worksheet.  I
sometimes declares dates in VBA as Single so unexpected reults don't
occur.

#2) I agree and that is what I said earlier.  You can easily get the
Year month and date of the number by using the Year(), Month(), and
Day(), functions.


MyYear = Year(42345)
MyMonth = Month(42345)
MyDay = Day(42345)

or
Dim MyDate as single
MyDate = 42345
MyYear = Year(MyDate)
MyMonth = Month(MyDate)
MyDay = Day(MyDate)


or a string
DateStr = Format(42435,"MM/DD/YY")




#3) Why do you care if a number was entered as a date or a number?  A
date is stored exactly like a single precision number.  The only
different between the two is the way they are displayed.


-- 
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=160426

[url="http://www.thecodecage.com"]Microsoft Office Help[/url]

joel wrote:
> 
> #2) I agree and that is what I said earlier.  You can easily get the
> Year month and date of the number by using the Year(), Month(), and
> Day(), functions.

In c++?

> 
> 
> #3) Why do you care if a number was entered as a date or a number? 

It is needed in my program.

User can enter a number or a date and based on that different 
calculation is done.

I would think that C++ in windows would use the same libraries as VBA
and store date the same way.  All you shold have todo is over-ride the
varible type and pass the number into a C++ function like MyDay =
day(Date 42345)

If this doesn't work then you can use the VBA function like this


obj = excel.application 'not usr eof the exact syntax
obj.application.evaluate("Day(42345)")


or
obj = excel.application 'not usr eof the exact syntax
obj.worksheetfuncttion.Day(42345)



YOu are going to have to give the user an option box to choose either a
number of a date.  Or have the user enter the date in mm/dd/yy format
and tthen search for the forward slash in the string.  If you havve the
forward slash it would be a date otherwise a number.


-- 
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=160426

[url="http://www.thecodecage.com"]Microsoft Office Help[/url]

joel wrote:
> I would think that C++ in windows would use the same libraries as VBA
> and store date the same way. 
> All you shold have todo is over-ride the
> varible type and pass the number into a C++ function like MyDay =
> day(Date 42345)

C++ does not have Date type.


> 
> If this doesn't work then you can use the VBA function like this
> 
> 
> obj = excel.application 'not usr eof the exact syntax
> obj.application.evaluate("Day(42345)")
> 
> 
> or
> obj = excel.application 'not usr eof the exact syntax
> obj.worksheetfuncttion.Day(42345)
> 

Too slow, and I do not want to link C++ back to Excel. It must be 
independent. The interaction is done using XLOPER


> 
> 
> YOu are going to have to give the user an option box to choose either a
> number of a date.

It does not work.
User can paste data in any way or mixing them.


   Or have the user enter the date in mm/dd/yy format

Date can be entered in any local format used. It used globally around 
the World. So I can't assume any date format. Excel does that 
interpretation form based on local settings.


> and tthen search for the forward slash in the string.  If you havve the
> forward slash it would be a date otherwise a number.
> 
I don't have access to date entered as string.
The only way Excel passes information to XLL function is integer 
representing date.

if you use printf to output the date in XLOPER using a %i format don't
you get the same number that excel would produce.

You are asking the impossible for the XLOPER to distiquish between a
date and a number where both are integers.  Maybe you want to pass two
parameters.


-- 
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=160426

[url="http://www.thecodecage.com"]Microsoft Office Help[/url]

joel wrote:
> if you use printf to output the date in XLOPER using a %i format don't
> you get the same number that excel would produce.

there is no date type in xloper. double(integer) only. For both: numbers 
and dates (it means internal representation)

> 
> You are asking the impossible for the XLOPER to distiquish between a
> date and a number where both are integers.

that what I am asking for. "Impossible" is also a valid answer but I 
want to check if there is really no other solution.


> Maybe you want to pass two
> parameters.
> 
that can be acceptable.
At Excel level I can add another range, checking if values entered into 
first one are dates or not.
It gives me true/false values at XLL level so I can find if 
corresponding number was date or number.
It requires one parameter more in XLL function and one range more in 
Excel, but does not requires changes in other parts of program.

At VBA level it was easy

if isdate (param) then
else
end if

Dates in excel are stored a a single precision number 24 bit IEEE
standard and uses 4 bytes (32bits) to store the data.  Integers were 16
bits (2 byes) but I'm told they are being stored as long which is also
32 bit.  Characters are 1 byte (8 bit) per character but I believe the
actual storage of the string has the byte count before the actual data
(I don't think you actually see the byte count when it is passed to
another function).


-- 
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=160426

[url="http://www.thecodecage.com"]Microsoft Office Help[/url]

joel wrote:
> Dates in excel are stored a a single precision number 24 bit IEEE
> standard and uses 4 bytes (32bits) to store the data.  Integers were 16
> bits (2 byes) but I'm told they are being stored as long which is also
> 32 bit.

including time in it, date is stored as double.
and there is no way to distinguish if number passed to c++ procedure is 
a real double or a date.
That what i was asking for. if there is really no way to distinguish 
that or simply I don't know how to do that.

and procedure to convert number into date and date into number is simple 
but requires a little of bit o work. That procedure is of course 
included in Excel but not available as "source code", so I was asking if 
that information is somewhere so I don't have to write what is already 
written.

We are at the beginning.




   Characters are 1 byte (8 bit) per character but I believe the
> actual storage of the string has the byte count before the actual data

yes.

> (I don't think you actually see the byte count when it is passed to
> another function).
> 

strings are passed as pointers and must be prepared the same way in 
XLOPER. First byte represents string length.