0

I'm importing data from Excel to MATLAB. My data in Excel is in [h]:mm:ss format, but when after importing to MATLABthe data becomes a decimal number.

Is there a way to convert this data back to an [h]:mm:ss format in MATLAB?

Some examples:

21:45:05 equals 0.906307870370370
21:14:31 equals 0.885081018518519
213:17:56 equals 8.88745370370370

I understand the conversion being a multiple of 24h be it in seconds, minuites or hours - which would imply 12:00:00 = 0.5. But I don't understand how I could convert the data back, i.e. 0.75 becomes 18:00:00 etc?

I've tried New_Nun(i,j) = datestr(nun(i,j),'HH:MM:SS'); to do this conversion, but I get this error -

Unable to perform assignment because the indices on the left side are not compatible with the size of the right side.

Would this be because my nun variable has NaN's in?

Adriaan
  • 17,741
  • 7
  • 42
  • 75
CptGoodar
  • 303
  • 2
  • 15
  • I think the RHS of your command runs, but that gives a string, which I'd say does not fit in `New_Nun(i,j)`. Does the RHS run, and if so, what comes out? – Adriaan Oct 17 '18 at 08:56
  • @Adriaan that RHS runs - until i reach a NaN at which point it stops - so are you saying the definition for New_Nun needs to be changed to a Cell variable? – CptGoodar Oct 17 '18 at 08:58
  • Ah, if it runs fine until it encounters a `NaN` value, just check for that and discard those, e.g. `nun(isnan(nun))=[]` – Adriaan Oct 17 '18 at 09:00
  • Note that you've used the wrong datetime format string... `HH:MM:SS` is `24hr hours : 2 digit month : 2 digit factional second`! You probably want something more like `HH:mm:ss`, read the documentation! Also note that Matlab's date numbers (the decimal value) is defined as " the number of days from January 0, 0000", I'm not sure if your Excel-imported data will correspond to this. – Wolfie Oct 17 '18 at 09:00
  • @Adriaan Done that via `isnan(nun(i,j)) == 0` and an if loop - although yours is a neater solution Thank you! – CptGoodar Oct 17 '18 at 09:01

1 Answers1

1

Note that datestr outputs a string, while the New_Nun(i,j)=...assignment expects a single value.

datestr(nun(i,j),'HH:MM:SS') outputs a [8 x 1] char array, which will not fit into New_Nun(i,j). And, by the way, 'HH:MM:SS' is indeed the correct argument for hours, minutes and seconds.

You may rather use a cell for New_Nun, which can contain any kind of data including strings of arbitrary length :

%Initialize random values with a NaN
nun=rand([5 5]);
nun(2,5)=NaN;

New_Nun = cell(size(nun));  %Initialize the cell array
not_a_nan = ~isnan(nun);    %Selection valid values
New_Nun(not_a_nan) = cellstr(datestr(nun(not_a_nan),'HH:MM:SS')); %Fill the cell array

disp(New_Nun)    
'13:47:28'    '23:37:19'    '06:00:28'    '13:24:09'    '12:38:05'
'00:55:58'    '08:34:58'    '04:25:44'    '11:14:46'            []
'13:17:25'    '10:34:02'    '11:17:16'    '04:42:18'    '05:51:32'
'20:40:44'    '18:30:16'    '04:29:22'    '17:43:03'    '22:05:24'
'16:36:35'    '10:17:18'    '22:45:41'    '16:04:17'    '07:06:39'

The NaN value has been converted to an empty cell. It could be replaced with any default value, e.g. New_Nun(~not_a_nan)={'00:00:00'};

This code does not handle hours above 24, as datestr. For than, hours need to be treated separately.

%Initialize random values with a NaN and one value larger than 24h
nun=rand([5 5]);
nun(2,5)=NaN;
nun(3,4)=nun(3,4)+10;   % One value above 24h

New_Nun = cell(size(nun));  %Initialize the cell array
not_a_nan = ~isnan(nun);    %Selection valid values

mmss = cellstr(datestr(nun(not_a_nan),'MM:SS')); %Write only MM:SS
hh   = floor(nun(not_a_nan(:))*24); %Get hours, and make it a column vector

%Now, add the hours
hours_not_zero = hh~=0; % Optional: used to display mm:ss if hh=0
mmss(hours_not_zero) = cellfun(@(h,m) sprintf('%02d:%s',h,m),num2cell(hh(hours_not_zero)),mmss(hours_not_zero),'uniformoutput',false);


New_Nun(not_a_nan) = mmss;
disp(New_Nun)
'02:53:42'    '08:38:56'    '12:07:03'    '15:28:54'    '12:49:18'
'16:21:15'    '03:44:23'    '01:22:31'    '20:43'               []
'02:36:04'    '11:08:51'    '19:51:35'    '244:45:48'   '33:55'   
'16:14:08'    '08:04:01'    '12:27:22'    '08:17:48'    '06:16:15'
'10:41:08'    '03:16:19'    '04:47:15'    '04:33:37'    '20:36:42'

This time, the value 35:34:11 is represented correctly. Not significant hours are not displayed, such as '20:43' for '00:20:43'.

Brice
  • 1,560
  • 5
  • 10