Writing data type calendarDuration to Excel?

2 次查看(过去 30 天)
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

回答(1 个)

Peter Perkins
Peter Perkins 2017-11-20
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.
  3 个评论
Peter Perkins
Peter Perkins 2017-11-29
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
Aram Schiffman 2017-11-29
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

请先登录,再进行评论。

类别

Help CenterFile Exchange 中查找有关 Calendar 的更多信息

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by