converting dates in textfile

Hello,

I am having problems trying to covert the dates from the first column on a textfile, called A

1880	                
1880.08340000000	
1880.16660000000	
1880.25000000000	
1880.33340000000	
1880.41660000000	
1880.50000000000	
1880.58340000000	
1880.66660000000	
1880.75000000000	
1880.83340000000	
1880.91660000000	

Date = datenum(A{:,1});
Date = datetime(Date,'ConvertFrom','datenum');

The output gives:

'22-Feb-0005 00:00:00'
'22-Feb-0005 02:00:05'
'22-Feb-0005 03:59:54'
'22-Feb-0005 06:00:00'
'22-Feb-0005 08:00:05'
'22-Feb-0005 09:59:54'
'22-Feb-0005 12:00:00'
'22-Feb-0005 14:00:05'
'22-Feb-0005 15:59:54'
'22-Feb-0005 18:00:00'

This is obviously incorrect.

The dates in the raw textfile look like this:

Maybe I need to round the column to X number of decimal places BEFORE using the datenum function?

If so, how should I do this?

Thank you.

Answer 1

[Walter Roberson]

>> datetime(floor(A),1,1) + years(mod(A,1))

ans = 

  12×1 datetime array

   01-Jan-1880 00:00:00
   31-Jan-1880 11:04:09
   01-Mar-1880 20:23:08
   01-Apr-1880 07:27:18
   01-May-1880 18:31:27
   01-Jun-1880 03:50:26
   01-Jul-1880 14:54:36
   01-Aug-1880 01:58:45
   31-Aug-1880 11:17:44
   30-Sep-1880 22:21:54
   31-Oct-1880 09:26:03
   30-Nov-1880 18:45:02

[Sarah Yun]

Hello,

Thank you for your reply.

Please can you explain what that code does?

Then I can accept your answer.

(Apologies, I am a beginner).

[Walter Roberson]

I guessed that the entries are whole years and fractions of a year.

datetime() does not provide any direct conversion for years and fractions of a year. We could try

datetime([0 1 1]) + years(A)

to give us that many years since January 1 of year 0, but if we do then we would see that the first entry would be

31-Dec-1879 21:36:00

instead of the expected 1-Jan-1880 00:00:00 . The difference of several hours and minutes reflects the fact that the years() function uses fixed 365 day years, rather than calendar years.

We could try

datetime([0 1 1]) + calyears(A)

but this will fail, because calyears() only accepts integers.

Therefore you need to break A up into full years and fractions of a year. You use the full years to create a base date with datetime() and then you add on fractions of a year.

Speaking of calendar years: 1880 happens to be a leap year, so it becomes ambiguous as to whether the fractions of a year are fixed length years or fractions of the individual year. You should consider using

datetime(floor(A),1,1) + days(mod(A,1)).*(365+leapyear(floor(A)))

However if you do not happen to have the Aerospace Toolbox you will need to write your own leapyear function such as by following https://www.mathworks.com/matlabcentral/answers/383870-writing-a-leap-year-function-without-using-the-leapyr-function#answer_306228

or you could code

baseyear = datetime(floor(A),1,1);
year_length = days(baseyear + calyears(1) - baseyear);
baseyear + days(mod(A,1)) .* year_length

The baseyear + calyears(1) part advances to a specific date, the first of the next year, and subtracting off baseyear again gives a duration object that by default is formatted in hours. days() converts that number of hours into whole numbers of days. The whole number of days appropriate for that year is then multiplied by the year fraction in days in order to get the offset from the beginning of the year.