| Author |
Message |
rlassalle
Groupie
Joined: 09 Aug 2012
Online Status: Offline
Posts: 57
|
  
Topic: Convert datetime to number
Posted: 12 Nov 2012 at 9:41am |
Hi there,
A datetime field value has to be part of a calculation how to convert it to be used in this formula;
Dim nProj_Dolar as number
nProj_Dolar = ({'RAM_Perf_'.CURR_$}-(({'RAM_Perf_'.BOM_$} - {'RAM_Perf_'.CURR_$}) / {'RAM_Perf_'.Thru Date} ) * 18)
formula = nProj_Dolar
{'RAM_Perf_'.Thru Date} = 6/20/12 in Crystal format that I have in the report. (value of the field)
in excel 6/20/2012 = 41080 if convert to number
Thanks
|
 IP Logged |
|
|
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 12 Nov 2012 at 10:54am |
Try subtracting 12/30/1899 from the date to get the number that Excel converts the date to. Your formula would look something like this: ({'RAM_Perf_'.CURR_$}-(({'RAM_Perf_'.BOM_$} - {'RAM_Perf_'.CURR_$}) /({'RAM_Perf_'.Thru Date} - Date(1899, 12, 30) )) * 18 -Dell
|
|
|
| IP Logged |
|
rlassalle
Groupie
Joined: 09 Aug 2012
Online Status: Offline
Posts: 57
|
Posted: 12 Nov 2012 at 12:46pm |
Thanks look well. But what happend if I do not have the information from exel just the value in the field DB. Hoe to convert any datetime type at a number without compare with another date.
Thanks
|
| IP Logged |
|
comatt1
Senior Member
Joined: 19 May 2011
Online Status: Offline
Posts: 337
|
Posted: 13 Nov 2012 at 3:05am |
|
SELECT CAST(CONVERT(datetime,'2009-06-15 23:01:00') as float)
|
| IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 13 Nov 2012 at 3:29am |
You can still subtract 12/30/1899 from the date to get the number that Excel uses. Or, you can use comatt1's logic to create a SQL Expression field - take out the "SELECT" and just use the syntax for your database (his example is SQL Server) to create the field that is a number instead of a date. -Dell
|
|
|
| IP Logged |
|
rlassalle
Groupie
Joined: 09 Aug 2012
Online Status: Offline
Posts: 57
|
Posted: 13 Nov 2012 at 8:06am |
My report is connected to an excel sheet, I do know how to use a SQL statement to get info from a excel file. Can you tell me how to do it?
What this mean, you can use comatt1's logic to create a SQL Expression field. How can I do that?
Thanks
|
| IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 13 Nov 2012 at 8:14am |
Since you're connected to an Excel sheet for data, I don't think that you can - SQL Expression fields are for getting databases to do some work without having to write a command. For example, I've written a report against tables that have an address in multiple fields, including several optional fields. Instead of creating a Crystal formula to walk through the options to get the complete address, I create a SQL Expression that calls a stored function in the database to format the data for me. -Dell
|
|
|
| IP Logged |
|
comatt1
Senior Member
Joined: 19 May 2011
Online Status: Offline
Posts: 337
|
Posted: 13 Nov 2012 at 8:15am |
|
switch the date value you export to excel with
formula field
that has
CAST(CONVERT(datetime,'2009-06-15 23:01:00') as float)
I think there is an excel formula that takes a date_text value and changes it to some number, datevalue(date_text) never used though
|
| IP Logged |
|
rlassalle
Groupie
Joined: 09 Aug 2012
Online Status: Offline
Posts: 57
|
Posted: 14 Nov 2012 at 2:21pm |
|
|
| IP Logged |
|
|
|
