Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
XLL, C++ and dates
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
XLL, C++ and dates
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/sh...d.php?t=160426 Microsoft Office Help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
XLL, C++ and dates
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
XLL, C++ and dates
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/libr...81(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/sh...d.php?t=160426 Microsoft Office Help |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
XLL, C++ and dates
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/libr...81(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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
XLL, C++ and dates
#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/sh...d.php?t=160426 Microsoft Office Help |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
XLL, C++ and dates
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
XLL, C++ and dates
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/sh...d.php?t=160426 Microsoft Office Help |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
XLL, C++ and dates
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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
XLL, C++ and dates
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/sh...d.php?t=160426 Microsoft Office Help |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
XLL, C++ and dates
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
XLL, C++ and dates
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/sh...d.php?t=160426 Microsoft Office Help |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
XLL, C++ and dates
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking computer dates (time) to spreadsheet dates that have formu | Excel Worksheet Functions | |||
Stop dates from showing as numbers - when formated as dates | Excel Discussion (Misc queries) | |||
compare 2 tables of dates to find the preceding dates | Excel Worksheet Functions | |||
how do I sort a column of random dates into Consecutive dates | Excel Worksheet Functions | |||
Toggle a range of Julian dates to Gregorian Dates and Back | Excel Programming |