Help with converting decimal dates in a loop

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);

[dpb]

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]

Given the large amount of data and the time constraints of my sanity, I would go with 365.25. It won't exactly be the correct date 100% of the time, but it will get me close enough. If you really want to get technical, the first leap year was started in 1904, so every four years from then, the number would be 366. Prior to then, it would need to be 365. I have absolutely no idea how to code that in, but if you can quickly do it, please go for it. Otherwise it is fine.

Thanks for the insight.

[Walter Roberson]

??

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]

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]

I never would have expected this to turn into a discussion about leap years. Anywho, for the purposes of my data, a day or two in either direction will not make much a difference, except if over time, these differences add up. For arguments sake, let's just go with my record starts at year 1 and increases at random intervals until 1999. If leap years can be easily accounted for, then by all means go for it.

[C G]

dpb, Your code, how is it meant to be used? Is it meant to give me the date time group and fix the matlab date number conversion?

[C G]

It is meant to spit out both a date time group and a matlab date number. 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.

[dpb]

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.

[C G]

Date time group is what your code is giving. The output is a day-month-year hour:min:sec group. There is something called a matlab date code or matlab time stamp. I am thinking it is a count of the number of days since some start point. Not really sure though. My decimal date 1998.#### corresponds to 16 Dec 1998, or matlab time stamp 730105. The code can achieve the date time group in a loop, just not the matlab time stamp in the same (or any) loop.

[dpb]

U "don't need no steenkin' loop" -- if you want hr,min,sec from the datetime, use

dv=datevector(dt);

[C G]

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]

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]

Please so not close questions that have an Answer.

Answer 1

[C G]

 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);

[Walter Roberson]

Please so not close questions that have an Answer.

[dpb]

"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
>>

Answer 2

[dpb]

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.

[C G]

I couldn't get your leap year function to work, but other than that, it seems like your code gives me the same output as n = datestr(datenum(DecDate,1,0)); From here, how do I get to the matlab date values?

The decimal dates are interpreted from year markers. Not much more to it than that. They are not meant to get us down to the sec, just to the day...ish, but the interpolation spits out all the digits it can.

[dpb]

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...

[C G]

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;

[dpb]

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.

[C G]

This is the problem. The code works fine as a one off, but fails in the loop. See the error codes at the bottom of the code.

 DecDate=1998.95....

 DecDate =

   1.9990e+03

 >> n = datestr(datenum(DecDate,1,0))

 n =

    '12-Dec-1998 18:00:00'

 >> formatIn = 'dd-mmm-yyyy HH:MM:SS';
 >> p = datenum(n,formatIn)

 p =

   7.3010e+05

 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));
    %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', n);
 fprintf(fid,'%s', '      ');
 fprintf(fid,'%s\n', p);

 end
 fclose(fid);
 Error using datenum (line 189)
 DATENUM failed.

 Caused by:
    Error using dtstr2dtnummx
    Failed to convert from text to date number.

[dpb]

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]

C G's "Answer" moved here:

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]

@C G: loops are not required for that. Read dpb's comment again.

[C G]

Please be brief and simplistic. How do I convert 5000+ decimal dates to my two outputs? Please write out the code directly.

[C G]

Using dpb's code above does not achieve what I need. It gets me from input to output 1, but not to output 2. So far there hasn't been anything to get me to output 2.

[Walter Roberson]

"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.

[dpb]

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...

[C G]

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]

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]

It is a long column of decimal numbers, but sure. Here you are.

Answer 3

[dpb]

    >> 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...

Answer 4

[Walter Roberson]

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).

Answer 5

[C G]

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));