Writing data type calendarDuration to Excel?

I'm writing Matlab dates and times to Excel. I see that the function "exceltime" converts datetime data so that they can be written to Excel, but that function doesn't work on calendarDuration types. (calendarDuration is the result of applying caldiff). I realize I can use Excel to determine elapsed time, but I prefer to calculate it in Matlab and write it out to Excel. How do I convert calendarDuration types so they can be written to Excel?

Thanks, Aram

Answer 1

[Peter Perkins]

Aram, Excel's notion of time is more or less equivalent to a datenum in MATLAB (with a different offset). In other words, a count of days + fractional days, either from an implied offset of "0"-Jan-1900 (if you are talking about an absolute time), or just the raw count (if you are talking about elapsed time). I don't know all the details of Excel's internals, but my impression is that to distinguish absolute from elapsed time, you change the display format.

A calendarDuration is a much subtler thing, in effect a 3-tuple comprising a count of months, days, and seconds, and in general it's not possible to turn that into a single number (how many days in a month? it depends. How many seconds in a day? it depends). In that sense, a calendarDuration is more like a datevec, not a datenum. It may be that you've called caldiff to get just the number of days, and for special cases like that you can certainly write out something (probably you'd convert to numeric using the days function).

I think you'll need to say more.

[Aram Schiffman]

Thank you, Peter. Excellent points. Let me put the question another way. I would like the time difference to end up in an Excel spreadsheet. (Matlab is subtracting two values to determine how long something took. Answer is in minutes and seconds). It is not important to me that Excel recognize it as having units of time. It could be written as a text string, for example. I just need humans to be able to read it. So if, for example, I could cast it as a string (analogy for numbers would be num2str) then I could write it to my spreadsheet using xlswrite. Right now, I don't see any way to get it written to an Excel file.

Thanks for your help. Much appreciated.

Best, Aram

[Peter Perkins]

seconds(diff(t)), or minutes, or whatever, creates a numeric "single-unit" value that you can write to a spreadsheet. days would create a value that Excel would sort of be able to recognize as it's own time representation - I think you could set Excel's display format as something like hh:mm:ss for cells into which you've written.

[Aram Schiffman]

Thank you. I did forget to follow up on this. I came up with a brute force method last week, not elegant but at least it is functional.

"thisStartDateTime" and "thisEndDateTime" are datetimes containing the dates and times of interest.

My crude solution:

thisElapsedTime=time(caldiff([thisStartDateTime,thisEndDateTime]));

totSec=seconds(thisElapsedTime);

thisHr=floor(totSec/3600);

thisMin=floor(mod(totSec,3600)/60);

thisSec=mod(mod(totSec,3600),60);

thisElapsedTimeStr=[num2str(thisHr),':',num2str(thisMin),':',num2str(thisSec)];

Then I write thisElapsedTimeStr to Excel. Ugly, but functional.

best, Aram