Help with converting decimal dates in a loop
Show older comments
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
What is needed is the absolute definition of what the time fraction really, really is---is it relative to a 365-day year, the "average" 365.25 day or either 365/366 depending on the year?
IOW, what is the actual real date/time for the given example? With a definition, one can write an algorithm; without it's futile to even start.
On the presumption of a 365-day year, one gets
>> d= 1998.95918367347;
>> yr=fix(d);
>> secs=(d-yr)*365*24*3600;
>> dt=datetime(yr,1,0,0,0,secs)
dt =
datetime
16-Dec-1998 02:26:56
>>
C G
on 17 Jul 2018
Walter Roberson
on 17 Jul 2018
??
Leap years with the 365 day calendar were introduced in 45BC.
1896 was a leap year. 1900 was not a leap year because of the rule that centuries are only leap years if they are divisible by 400.
dpb
on 17 Jul 2018
I never heard that before and the ML datetime doesn't know about prior to 1900 not having Feb 29 so I'll let you deal with that however you so choose.
C G
on 17 Jul 2018
C G
on 17 Jul 2018
C G
on 17 Jul 2018
dpb
on 17 Jul 2018
It will print the date string for the dates to a file; looked like that's what your code was doing..
If there's other stuff to be done as well, incorporate as needed.
I have no klew what the second comment says--what's a "date time group"? My code gives a ML datetime; I strongly recommend against continuing use of the deprecated datenum; it just has too many warts besides being now the "red-haired stepchild" as far as TMW is concerned.
dpb
on 17 Jul 2018
U "don't need no steenkin' loop" -- if you want hr,min,sec from the datetime, use
dv=datevector(dt);
C G
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
Walter Roberson
on 19 Jul 2018
Please so not close questions that have an Answer.
Accepted Answer
C G
on 19 Jul 2018
2 Comments
Walter Roberson
on 19 Jul 2018
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
>>
More Answers (4)
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
C G
on 17 Jul 2018
dpb
on 17 Jul 2018
I've had no issues w/ isleapyr for almost 20 years (so to speak :) ). What's the specific problem?
>> yr=[1922:1958];
>> sum(isleapyr(yr))
ans =
9
>> yr(isleapyr(yr))
ans =
1924 1928 1932 1936 1940 1944 1948 1952 1956
>>
Seems to work fine...
dpb
on 17 Jul 2018
Put the code in an m-file named isleapyr.m like any other function--the "type leapyr" was the command to print the function on the screen to paste...not the code.
I really can't tell what it is you're trying to do, sorry...
As noted above you don't need a loop to process a series of dates either as date strings or the funky numbers you started with.
For one thing, you read the file as an array and then convert to cell which you then have to turn around and dereference -- that's a lot of mess for no discernible reason.
Then you take the input number, convert it to a string and then convert that string back to numeric -- that, too, seems like wasted motion.
The actual error itself, however, is owing to the fact that apparently there's some bum input in that the string being passed isn't possible to be parsed by the input format. Again, why that would be isn't discernible without seeing the data itself.
How about let's step back, define clearly the input and desired output from basics and then attach the data. Earlier you wrote "I have a whole host of different programs that need different versions of the same date. I really hate that fact, but instead of recoding each program, I thought it would be easier to create different dates for each program."
How about actually explaining this underlying problem you're trying to solve and let's attack it instead trying to debug nonworking code?
Stephen23
on 18 Jul 2018
I am not sure if this is you not getting something simple or me not explaining it clearly.
I am not concerned with leap years at all. I have no idea how my question divulged into that.
What I need is a robust method that can take one input and give me two outputs.
Input - decimal date
Output 1 - Day/month/Year
Output 2 - MATLAB date code
Example:
Input - 1998.9549
Output 1 - 16/Dec/1998
Output 2 - 730105
I can achieve a loop from Input to output 1, but not to output 2. I can get to output 2 with some fiddling. I need to import the data from loop 1 in to a separate variable, then run a separate loop. This method is not worthwhile as it means I will need to do extra steps and there it can lead to mistakes.
Again, what I need is a loop that works directly. Input to output 1 AND output 2.
Stephen23
on 18 Jul 2018
@C G: loops are not required for that. Read dpb's comment again.
C G
on 18 Jul 2018
Walter Roberson
on 18 Jul 2018
"What I need is a robust method that can take one input and give me two outputs."
But we cannot give you a robust method without knowing what the representation is for leap years. We can only give you a non robust method, such as
Input1 = 1998.9549;
Output2 = datenum(floor(Input(:)), 0, 1) + (Input - floor(Input)) * 365.5;
Output1 = datestr(Output2, 'dd/mmm/yyyy');
Unfortunately for you, this calculation cannot possibly agree with
Input = 1998.95918367347
also being on December 16, 1998. Those two dates are 0.00428367347 years apart, which is more than 1.5 days in the range of 365 days per year, so even if the 1998.9549 was intended to be first thing on December 16, 1998, then 1998.95918367347 has to be after noon on December 17, 1998.
If you change the 365.5 to make 1998.95918367347 come out on December 16, 1998, then you cannot possibly have the 1998.9549 also come out on December 16, 1998.
By the way, to have 1998.95918367347 come out on December 16, 1998 then your years have to be strictly less than 364.9 days long -- and if you round that to 365 then you cannot get December 16, 1998 out of it.
Well, the Q? of leap year came up in determining how to decipher the input time fraction; what the answer is is dependent upon what the assumption is in creating it.
The uncertainty is twofold; firstly you never did really unequivocally answer that question. The solution given uses the length of the given year altho that can be easily modified.
Secondly, I was trying to see if we could address the underlying problem that gave rise to this Q? in a more expeditious manner.
As noted above, you're going through a lot of machinations that aren't needed to do the above given the initial input of reading the spreadsheet.
As noted in the initial Answer, if you simply use (the deprecated) datenum instead of (recommended) datetime
A = xlsread('Example.xlsx');
dates=A(:,5);
yr=fix(dates);
secs=(dates-yr).*(365+isleapyr(yr))*24*3600;
dn=fix(datetime(yr,1,0,0,0,secs));
ds=datestr(dn,24);
If this fails on some value, that's in the input data and would have to have it to see why.
BUT, the time fraction of 0.9549 does NOT correspond to Dec 16, but Dec 14 so the result for your input will be different by two days. The initial sample input of 1998.95918367347 does correspond to Dec 16, so perhaps this is a typo?
As far as the complaint about "Output 2"; if you were reading the output in ML, then the datetime value would also be imported as a unique datetime that can be compared just as can (the deprecated) datenum. It just doesn't have a simple floating point representation but there really doesn't seem there should be any need for that; it was trying to get to the end use I was prodding for...
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'
>>
C G
on 19 Jul 2018
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...
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).
C G
on 30 Aug 2018
Categories
Find more on Data Type Conversion in Help Center and File Exchange
