| T O P I C R E V I E W |
| GaussianFit |
Posted - 02/15/2013 : 3:10:15 PM
Origin Ver. and Service Release (Select Help-->About Origin): 8.1 SR3
Operating System: Windows 7
I have a worksheet column populated with date strings, such as "02/15/2013", is there a built-in function that can convert the date strings to a floating number like 2013.126? And how to convert it back to a date string? In Matlab there is a function datenum('02/15/2013') = 735280 which converts a date to a serial date number, but that is not what I wanted. Thanks a lot for any suggestions. |
| 13 L A T E S T R E P L I E S (Newest First) |
| greg |
Posted - 02/21/2013 : 5:49:38 PM
Since a true Date column in Origin has an underlying numeric value, you can use it for plotting and analysis without any special conversion, so I wouldn't even bother creating these 'floating numbers'.
If you just change the Display Format between Date and Numeric you can see that your dates are already numbers. Plotting vs Dates will result in correct spacing; Analysis using Dates will see your data as units of Days ( in FFT for example ).
If you really need the 'floating numbers', put them in an additional column and keep your dates, otherwise you'll have to figure out the code to get back to dates from those numbers. |
| GaussianFit |
Posted - 02/20/2013 : 3:37:29 PM
quote:
Originally posted by greg
I used 0 to (D-1)/TotalDaysInAYear.
Hideo used 1 to D/TotalDaysInAYear.
Your choice.
Got you. It wouldn't matter as long as I can convert it back to correct dates. The reason I wanted to convert a date to a floating number was to use floating numbers for plotting and curve fitting. In such a case it's much easier to use a floating number as independent for numerical analysis. At the end of the day I still need to convert the floating numbers back to real dates, how would this be done? |
| greg |
Posted - 02/20/2013 : 1:04:27 PM
I used 0 to (D-1)/TotalDaysInAYear.
Hideo used 1 to D/TotalDaysInAYear.
Your choice.
|
| GaussianFit |
Posted - 02/20/2013 : 12:54:16 PM
quote:
Originally posted by greg
To clarify..
If your date strings are in column A and you want your numbers in column B then this script does the trick:
range ra1 = col(A);
range ra2 = col(B);
loop(ii,1,ra1.GetSize())
{
str$ = ra1[ii]$;
ra2[ii] = year(date(%(str$),"yyyy-MM-dd"))
+ (day(date(%(str$),"yyyy-MM-dd"),2) - 1)
/ day(date(12/31/$(year(date(%(str$),"yyyy-MM-dd")))),2);
}
This script did worked, but what's puzzling is that it gave a slightly different number than the year and day function that Hideo used: year(col(1))+day(col(1),2)/365
For 2013-02-05,
Your script returned 2013.09589041096
Hideo's formula returned: 2013.09863013699
Is that because of leap years?
After all, thanks a million for helping out! |
| GaussianFit |
Posted - 02/20/2013 : 12:48:47 PM
quote:
Originally posted by greg
To clarify..
When you have a string like "2013-02-05" in a Text & Numeric column, you must convert that to a Date column for proper display and analysis.
You can convert directly from Text to Date using Column Properties.
Double-click at the top of the column to open Column Properties.
Change the "Format" to "Date".
Change the "Display" to "Custom Display".
Set the "Custom Display" string to yyyy-MM-dd or yyyy-dd-MM
(lower case y, upper case M, lower case d) whichever is correct.
Click OK.
Yes that's what I did, if I have a string like "2013-02-05" in a text/numeric column, I can convert it to date format only by setting the custom display to yyyy'-'MM'-'dd. None of the other variants worked, such as yyyy-MM-dd, or yyyy'-'mm'-'dd. |
| greg |
Posted - 02/20/2013 : 10:21:28 AM
To clarify..
When you have a string like "2013-02-05" in a Text & Numeric column, you must convert that to a Date column for proper display and analysis.
You can convert directly from Text to Date using Column Properties.
Double-click at the top of the column to open Column Properties.
Change the "Format" to "Date".
Change the "Display" to "Custom Display".
Set the "Custom Display" string to yyyy-MM-dd or yyyy-dd-MM
(lower case y, upper case M, lower case d) whichever is correct.
Click OK.
If the conversion is successful, your date strings will now be right justified and will have an internal numeric value similar to a date serial number: date(2/15/2013 12:30) = 2456338.5208333, where the integer part is days and the fractional part is fraction of a day (time). You can now change the Display to whatever you like - "Wed, Feb 20" - without affecting the actual Date value.
I don't know of what use a number like 2013.126 would be (certainly not as useful as a true Date), but if you really need that then you can use something like:
2013 + (day(date(2/15/2013),2) - 1) / day(date(12/31/2013),2)
The date function also has a variant:
date(2013-02-15,"yyyy-MM-dd")
and in addition to the day function, there is a year function.
If your date strings are in column A and you want your numbers in column B then this script does the trick:
range ra1 = col(A);
range ra2 = col(B);
loop(ii,1,ra1.GetSize())
{
str$ = ra1[ii]$;
ra2[ii] = year(date(%(str$),"yyyy-MM-dd"))
+ (day(date(%(str$),"yyyy-MM-dd"),2) - 1)
/ day(date(12/31/$(year(date(%(str$),"yyyy-MM-dd")))),2);
}
|
| GaussianFit |
Posted - 02/19/2013 : 6:07:12 PM
quote:
Originally posted by Hideo Fujii
Hi GaussianFit,
>> do I need to first convert column A from text/numeric to date?
date(col(1)$,"yyyy'-'mm'-'dd");
converts the text string to date, so you don't have to convert to data. This formula does. I'm not sure why it didn't work. In my test it worked.
By the way, you can import the date data to the Date-type column directly (with the Custom Date Format, if needed) either in Import Wizard or Import ASCII.
--Hideo Fujii
OriginLab
Tried both import wizard or import ascii, but always got column (A) as text/numeric, I changed it to date by custom format as: yyyy'-'MM'-'dd, then tried the formula you gave, but it still didn't work. Don't know why. |
| Hideo Fujii |
Posted - 02/18/2013 : 2:37:00 PM
Hi GaussianFit,
>> do I need to first convert column A from text/numeric to date?
date(col(1)$,"yyyy'-'mm'-'dd");
converts the text string to date, so you don't have to convert to data. This formula does. I'm not sure why it didn't work. In my test it worked.
By the way, you can import the date data to the Date-type column directly (with the Custom Date Format, if needed) either in Import Wizard or Import ASCII.
--Hideo Fujii
OriginLab
|
| GaussianFit |
Posted - 02/16/2013 : 11:55:56 AM
quote:
Originally posted by Hideo Fujii
Hi GaussianFit,
This is an opposite case, to convert a formatted string to a (internally implemented) date value. You can use "Date()" function in such case. Try the following in the Set Column Values tool:
date(col(1)$,"yyyy'-'mm'-'dd");
--Hideo Fujii
OriginLab
Thanks, I tried this one but it didn't work, do I need to first convert column A from text/numeric to date? |
| Hideo Fujii |
Posted - 02/15/2013 : 6:13:51 PM
Hi GaussianFit,
This is an opposite case, to convert a formatted string to a (internally implemented) date value. You can use "Date()" function in such case. Try the following in the Set Column Values tool:
date(col(1)$,"yyyy'-'mm'-'dd");
--Hideo Fujii
OriginLab |
| GaussianFit |
Posted - 02/15/2013 : 5:45:58 PM
quote:
Originally posted by Hideo Fujii
Hi GaussianFit,
You can easily make a function by combining the built-in functions, "Year()" and "Day()". For example, if your date data in the 1st column, and want to convert it yyyy.fff format to store in other column, you can put the following formula in the Set Column Values tool:
("2" in the Day function is an option to return the day of year.)year(col(1))+day(col(1),2)/365;
Hope this is what you wanted.
--Hideo Fujii
OriginLab
I ran into some problem when the date is imported from a CSV file. The date in column A is displayed as 2013-02-05, the type is by default set as text/numeric, but the Year and Day functions only work when the type is set as date. I couldn't find a format that matches the date in column A and it returned an error message when trying to set column B value using the formula. |
| Hideo Fujii |
Posted - 02/15/2013 : 5:32:34 PM
P.S.,
If you no way, but need to deal with the leap yeas, instead of "365" in the above formula, you can use the following term:
365+(Year(col(1))-int(Year(col(1))/4)*4!=0?0:Year(col(1))-int(Year(col(1))/100)*100!=0?1:Year(col(1))-int(Year(col(1))/400)*400!=0?0:1) |
| Hideo Fujii |
Posted - 02/15/2013 : 4:05:47 PM
Hi GaussianFit,
You can easily make a function by combining the built-in functions, "Year()" and "Day()". For example, if your date data in the 1st column, and want to convert it yyyy.fff format to store in other column, you can put the following formula in the Set Column Values tool:
("2" in the Day function is an option to return the day of year.)year(col(1))+day(col(1),2)/365;
Hope this is what you wanted.
--Hideo Fujii
OriginLab |
|
|
