MATLAB Answers

C G
0

Help with converting decimal dates in a loop

Asked by C G
on 17 Jul 2018
Latest activity Answered by C G
on 30 Aug 2018
I am having issues with my loop. I can get the individual elements to work individually, but not together in the loop.
Here is my problem. I have a very long list of decimal dates, dates that look like 1998.95918367347. I know this corresponds to sometime on 16 Dec 1998, or MATLAB date 730105 or 730110, depending if the time is included. What I need is both day month year and the matlab date for different elements in my work.
I was able to convert the decimal dates to day, month, year, time just fine, even in a loop, but when I try to add the conversion to matlab date, the loop falls apart. When I try the second conversion outside the loop, it works fine. I am not sure what is going on. With some tweaking, I was able to narrow down which line is the problem. I think it has to do with the first conversion setting up my date in one format and the second conversion needing a different one, but I don't know how to fix it. With 5555 dates in this batch and other large batches coming soon, it would help to have it in a loop so I don't have to do each decimal date individually.
It would help to have this in a text file to share with others, so that is how the loop was designed.
If anyone knows of a shorter way to get both day,month,year and the matlab date, I would appreciate it. As far as the data does. I suggest starting with 1900.123456789 and then adding some random decimal places until you have a few samples.
I am also having a random issue with the matlab dates. Sometimes it pops up and other times it doesn't. There are some instances when it is working in the loop, where the matlab dates do not work earlier than year 178CE. It appears to be random.
Below is my code.
A = xlsread('Example.xlsx');
B = num2cell(A);
[fid,msg] = fopen(sprintf('Example3.txt'),'wt');
assert(fid>=0,msg)
for q = 1:5555
DecDate = B{q,5}; %This is the column with the decimal dates in it.
n = datestr(datenum(DecDate,1,0));
fprintf(fid,'%s\n', n);
DateString = datestr(datenum(DecDate,1,0));%This line is optional?
formatIn = 'dd-mmm-yyyy HH:MM:SS';
p = datenum(n,formatIn); %This is where the problem is.
%p = datenum(DateString,formatIn); %This line also caused problems.
fprintf(fid,'%s', ' ');
fprintf(fid,'%s', p);
end
fclose(fid);

  13 Comments

I don't want hr,min sec.
I want the Matlab date stamp. This one. This is the one I want.
DateString = '16-Dec-1998';
formatIn = 'dd-mmm-yyyy';
datenum(DateString,formatIn)
ans =
730105
dpb
on 17 Jul 2018
What's wrong with datetime? datenum has been deprecated.
But, if you insist, then just like for datestr,
dates=A(:,5); % convenient variable for dates column
yr=fix(dates); % year
secs=(dates-yr).*(365+isleapyr(yr))*24*3600; % seconds in year accounting for leap yrs
dn=datenum(yr,1,0,0,0,secs); % ML datenum
Please so not close questions that have an Answer.

Sign in to comment.

5 Answers

Answer by C G
on 19 Jul 2018
 Accepted Answer

A=[1.18750 1.90179 2.36709 2.87342 3.27273 3.86364 4.22727 4.51948 4.77922 5.06122 5.46939 5.87755];
yr=fix(A);
secs=(A-yr).*(365+isleapyr(yr)).*24.*3600;
dn=fix(datenum(yr,1,0,0,0,secs)).'
ds=datestr(dn) % Note: Struggles with BCE values
%Better to use the loop. It allows you to convert it easily to use in other programs.
A = xlsread('Example.xlsx');
B = num2cell(A);
[fid,msg] = fopen(sprintf('Example3.txt'),'wt');
assert(fid>=0,msg)
for q = 1:5555
DecDate = B{q,5};
n = datestr(datenum(DecDate,1,0));
fprintf(fid,'%s\n', n);
end
fclose(fid);

  2 Comments

Please so not close questions that have an Answer.
dpb
on 19 Jul 2018
"ds=datestr(dn) % Note: Struggles with BCE values"
Per documentation.
You might instead consider using juliandate which does handle BCE dates but is in days and fractions vis a vis years and fractions from its common epoch. I haven't taken the time to think much of conversion but datetime supports that _'ConvertFrom','juliandate' named argument.
>> datetime(0,'ConvertFrom','juliandate')
ans =
datetime
24-Nov--4713 12:00:00
>>

Sign in to comment.


Answer by dpb
on 17 Jul 2018
Edited by dpb
on 19 Jul 2018

A = xlsread('Example.xlsx');
[fid,msg] = fopen(sprintf('Example3.txt'),'wt');
assert(fid>=0,msg)
dates=A(:,5); % convenient variable for dates column
yr=fix(dates); % year
secs=(dates-yr).*(365+isleapyr(yr))*24*3600; % seconds in year accounting for leap yrs
dt=datetime(yr,1,0,0,0,secs); % ML datetime
fprintf(fid,'%s\n', dt); % write to file
fid=fclose(fid);
isleapyr is my utility function--
>> type isleapyr
function is=isleapyr(yr)
% returns T for input year being a leapyear
% dpbozarth Rev 0 07Jun1998 Initial
% Rev 1 31Mar2005 Use eomday() vs number days in year
% Rev 2 04Feb2015 Handle new datetime class
if isdatetime(yr), yr=year(yr); end
is=eomday(yr,2)==29;
If you really want to ignore before 1900, you can "fix" the function to return false but it'll screw up with Matlab whether use datenum or datetime as they both incorporate leap years in their calculations. It'd be easy-enough in just the function;
is=eomday(yr,2)==29 & yr>1900;
But then the ML functions will be off and it'll be compounded the farther you go back. I forget when the initial epoch for datetime is, but it's based on the ISO Standard. What and how that's related to your problem I've no klew... :)
Without those, your problem is much more of a pit(proverbial)a(ppendage) to deal with. Although it raises the question of who defined the values and what did they use is the key to decoding the values.

  15 Comments

Pick a method for determining a leap year or not. It doesn't matter to me. I just want to see someone write a code that can get from a decimal date to both day/month/year AND a matlab serial date in one go. I am not understanding why this is so difficult.
YES I KNOW THE EXAMPLES DON'T MATCH! They were never meant to. I was just trying to give an example of what I want. After displaying the values at least 3 times before, it hasn't worked, so why be accurate? Why try?
The matlab serial dates will never be accurate and I understand this. If you convert 16 Dec 1998 you get 730105. If you add a time, the value changes. I added some 2hr:5min:sec and wound up with 730110. Include time or don't.
Now that I am done venting. Please write a code that will convert a decimal date... ANY DECIMAL DATE... to BOTH day month year and matlab serial date. Pick a date, any date. Include leap years or not. No one really cares if the dates include leap years or not. I have included a list of over 100+ take the list, and convert the dates.
1.18750 1.90179 2.36709 2.87342 3.27273 3.86364 4.22727 4.51948 4.77922 5.06122 5.46939 5.87755 6.26168 6.63551 7.01010 7.41414 7.81818 8.34921 8.98413 9.68421 10.20000 10.56364 10.92727 11.27119 11.61017 11.94915 12.32075 12.69811 13.09524 13.57143 13.89881 14.36170 14.78723 15.23292 15.49689 15.74534 15.99379 16.30952 16.62698 16.94444 17.67347 18.21622 18.57658 18.93694 19.44000 19.97333 20.41304 20.84783 21.20805 21.61074 21.94631 22.65517 23.13158 23.65789 24.14141 24.54545 24.94949 25.27132 25.58140 25.89147 26.22609 26.57391 26.92174 27.24800 27.56800 27.92800 28.35644 28.75248 29.18750 29.68750 30.11194 30.41045 30.70896 31.01000 31.41000 31.91000 32.22930 32.67516 32.96178 33.44156 33.96104 34.39362 34.81915 35.29487 35.80769 36.24272 36.67961 36.97087 37.33333 37.75000 38.22222 38.77778 39.12698 39.33862 39.55026 39.76190 39.97354 40.38043 40.81522 41.25000 41.68478 42.44086 42.92473 43.27143 43.55714 43.84286 44.15789 44.50877 44.90351 45.11047 45.31395 45.54651 45.77907 46.02899 46.60870 47.20536 47.56250 47.91964 48.29524 48.67619 49.05714 49.43810 49.81905 50.11413 50.33152 50.54891 50.90217 51.36782 51.88506 52.37975 52.88608 53.25620 53.58678 53.91736 54.17964 54.41916 54.65868 54.89820 55.13295 55.36416 55.59538 55.82659 56.05405 56.27027 56.48649 56.70270 56.91892 57.19685 57.51181 57.96457 58.25503 58.52349 58.79195 59.08257 59.63303 60.00300 60.28571 60.57143 60.85714 61.18868 61.56604 61.94340 62.43590 62.94872 63.32143 63.67857 64.02963 64.32593 64.62222 64.91852 65.23577 65.56098 65.88618 66.30147 66.55882 66.85294 67.16393 67.49180 67.81967 68.19780 68.63736 69.03911 69.26257 69.48603 69.70950 69.93296 70.35443 70.86076 71.24576 71.66949 72.01351 72.55405 73.08046 73.54023 74.04098 74.28689 74.57377 74.90164 75.24731 75.56989 75.94624 76.42169 76.90361 77.31373 77.70588 78.11111 78.55556 79.00300 79.46512 79.93023 80.22078 80.48052 80.74026 81.02688 81.18817 81.37634 81.59140 81.80645 82.03704 82.40741 82.87037 83.22628 83.55474 83.91971 84.17623 84.48361 84.81148 85.16190 85.54286 85.97143 86.47436 86.92308 87.36559 87.79570 88.19811 88.57547 88.95283 89.32710 89.70093 90.07692 90.46154 90.84615 91.28916 91.77108 92.16935 92.49194 92.81452 93.11644 93.42466 93.66438 93.93836 94.27928 94.63964 95.00300 95.33898 95.67797 96.01961 96.41176 96.80392 97.11050 97.33149 97.55249 97.82873 98.09000 98.49000 98.94000 99.16783 99.41259 99.69231 99.97203
dpb
on 19 Jul 2018
Chill, dood.
Attach as a file, please.
The code is above. (Altho I forgot one .*)
>> A=[1.18750 1.90179 2.36709 2.87342 3.27273 3.86364 4.22727 4.51948 4.77922 5.06122 5.46939 5.87755];
>> yr=fix(A);
>> secs=(A-yr).*(365+isleapyr(yr))*24*3600;
>> dn=fix(datenum(yr,1,0,0,0,secs)).'
dn =
434
695
864
1049
1195
1411
1544
1651
1746
1849
1998
2147
>> ds=datestr(dn)
ds =
12×11 char array
'09-Mar-0001'
'25-Nov-0001'
'13-May-0002'
'14-Nov-0002'
'09-Apr-0003'
'11-Nov-0003'
'23-Mar-0004'
'08-Jul-0004'
'11-Oct-0004'
'22-Jan-0005'
'20-Jun-0005'
'16-Nov-0005'
>>
ADDENDUM
Forgot the specific output format for date...
>> ds=datestr(dn,24)
ds =
12×10 char array
'09/03/0001'
'25/11/0001'
'13/05/0002'
'14/11/0002'
'09/04/0003'
'11/11/0003'
'23/03/0004'
'08/07/0004'
'11/10/0004'
'22/01/0005'
'20/06/0005'
'16/11/0005'
>>
It is a long column of decimal numbers, but sure. Here you are.

Sign in to comment.


Answer by dpb
on 19 Jul 2018

>> t=xlsread('Example.xlsx');
>> [dn,ds]=convertTN(t);
>> whos t dn ds
Name Size Bytes Class Attributes
dn 5555x1 44440 double
ds 5555x1 733260 cell
t 5555x1 44440 double
>> save example t dn ds
>> dn(end)
ans =
737791
>> ds(end)
ans =
1×1 cell array
{'01/01/2020'}
>>
>> type convertTN
function [dn,ds]=convertTN(t)
% Returns ML datenum and datestr for input fractional year
yr=fix(t(:));
secs=(t-yr).*(365+isleapyr(yr))*24*3600;
dn=fix(datenum(yr,1,0,0,0,secs));
ds=cellstr(datestr(dn,24));
end
>>
Don't have a klew whether means anything or not, but processes the whole file w/o error...

  0 Comments

Sign in to comment.


Answer by Walter Roberson
on 19 Jul 2018

t = readtable('Example.xlsx');
Input = t{:,1};
Output2 = datenum(floor(Input(:)), 0, 1) + (Input - floor(Input)) * 365;
Output1 = cellstr( datestr(Output2, 'dd/mmm/yyyy') );
This produces dates in the year range 9998 for the three negative decimal dates in your input, -1.87109375 -1.51953125 -1.2265625. In order to correct that, you will need to tell us the date representation you want to use for BCE, and you also need to clarify whether an entry of -0.25 would mean 1/4 year before the beginning of the common era (so, September) or if -0.25 should mean 1/4 year into the year before the common era (so, April).

  0 Comments

Sign in to comment.


Answer by C G
on 30 Aug 2018

I improved upon the code a little bit. It is faster and easier to manage. What this code will do is generate a list of Decimal years from a obnoxiously long numeric matrix of data, known in this case as t1data. The first line in the for loop is to skip over the columns in the data that are not related to the date. The 1900 at the front is to make the decimal year take on 4 digits and be in the 20th century. Thankfully, my list does not cross into the 21st century. The first DateNumber uses all rows. The one in the loop focuses on the date for the second row only. For reference, there are 11 columns of data related to one decimal year per row. The columns are arranged like this, "Year, Month, Day, Hour, Minute, Second, Data, Data, Data, Data, Data". The sequence repeats itself horizontally for over 90000 columns.
DateNumber = 1900 + (decyear([t1Data(:,1),t1Data(:,2),t1Data(:,3),t1Data(:,4),t1Data(:,5),t1Data(:,6)]));
for j = 12:11:size(t1Data,2)
DateNumber_1(j) = 1900 + (decyear([t1Data(2,j),t1Data(2,j+1),t1Data(2,j+2),t1Data(2,j+3),t1Data(2,j+4),t1Data(2,j+5)]));
end
Running the for loop will create a single row with a date every 12th column. To transpose the row, simply use,
DateNumber_1 = DateNumber_1.';
To plot the datenumber with some data try this outside of the loop. This will create a scatter plot of whatever you are looking for and will skip all of the zeros in the DateNumber_1 dataset.
scatter(DateNumber_1(12:11:end),somedata(12:11:end));

  0 Comments

Sign in to comment.