CODEWITHSUNDEEP
excelmatlabdate
Josh Robertson
Josh Robertson

Reputation: 57

Convert numerical data in a cell structure to a timestamp (HH:MM:SS) MATLAB

I've imported an Excel spreadsheet into MATLAB which contains a column of timestamps I would like to extract. However, as Excel stores timestamps as numerical data, the imported cells are now in a non-string format such as 0.4479, 0.4480 etc. Is there a quick way to convert all cells to a HH:MM:SS format?

Upvotes: 0

Views: 185

Answers (2)

Pursuit
Pursuit

Reputation: 12345

Yep, datestr can handle this pretty easily.

Given:

numericCellData = {0.4479 0.4480}

Use:

datestr(cell2mat(numericCellData),'HH:MM:SS')

What we are doing:

  1. Use the cell2mat function to convert your cell array to a more typical numeric array. This is what the workhorse datestr expects.

  2. The datestr function is meant to perform this exact type of conversion, from numeric time to a string representation.

Your examples were an interesting specific case, where your values probably represented only time (all values were less than 1; you asked for HH:MM:SS format). For the more general case, of converting numeric date-and-time data from Excel to Matlab, you will usually need to adjust for the date offset used:

  • Matlab date numbers: Are in units of days, usually double precision, and the 0 value represents January 0, 0000. (Think, new years eve in an era when the Roman empire was the dominant political entity in Europe, as represented by the Gregorian calendar which had not yet been developed.)

  • Excel date numbers: Are also in units of days. However the number 0 represents January 0, 1900. (Or, new years eve, 1899). This is 693961 in the Matlab system, and sometimes you need to make the adjustment.

Upvotes: 1

Siva
Siva

Reputation: 1149

Select the Column > Right Click >Format Cells >Number Tab> Select Time under Category > Select the Type and click on Ok

Upvotes: 0

Related Questions