When converting datenum to datetime, days are off by 1 and years are incorrect
9 次查看(过去 30 天)
显示 更早的评论
Hello,
I'm having trouble in getting the wrong day/year when converting serial dattimes from excel to matlab. Basically, the date and time I get from excel (after changing with 'format'--> 'date') are slightly off from the Matlab answer I get. My Matlab dates are always 1 day later than what they should be compared to excel and the years are preceded by '01' vs '20' . I have a screenshot of the Matlab & excel (correct) datetimes & the code I used in Matlab, and the excel serial dates. Any insight on what could be wrong would be appreciated!
Thanks,
Ashley
![](https://www.mathworks.com/matlabcentral/answers/uploaded_files/1260345/image.png)
![](https://www.mathworks.com/matlabcentral/answers/uploaded_files/1260350/image.png)
%load Amanda's detection table
Amanda_detections= readcell('/home/ashley/Desktop/SEFSC_Projects_PhD/Final_Detector/Amanda_Tables/LongMoan/Harp/DC02_LongMoanDetection_Dates&Times_Verified_InTemplate.xlsx');
%convert detections to matlab serail date/times w/ magical #
starttimes=Amanda_detections(:,5);
starttimes(1)=[];
starttimes=cell2mat(starttimes);
date_time=datestr(starttimes(1:10,:),'yyyy-MM-dd HH:mm:SS')
3 个评论
dpb
2023-1-11
As per usual, it's nearly impossible to debug something from Excel without the actual spreadsheet data itself to see what is actually there...besides the pain of having to try to create a representative dataset from scratch.
So, "help us help you"; attach the spreadsheet or a small piece of one that illustrates the problem. Use the paperclip icon and attach it here...
采纳的回答
dpb
2023-1-11
Per documentation, Excel uses funky time conventions; use the conversion from Excel and all will be well...I saved only about 25 lines or so of the file (30K was a little more than we need to test with), and the following works and matches what get inside the workbook if I turn its display format to display down to seconds as times...
>> tDC=readtable('DC03.xlsx');
>> tDC
tDC =
24×15 table
InputFile EventNumber SpeciesCode Call StartTime EndTime Parameter1 Parameter2 Parameter3 Parameter4 Parameter5 Parameter6 Comments Image Audio
_________________________________________________________________________________ ___________ ___________ _____________________ _________ _______ __________ __________ __________ __________ __________ __________ ____________________ _____ _____
{'I:\DC\GofMX_DC02\GofMX_DC02_disks01-16_df100\GofMX_DC02_disks01-08_df100.ltsa'} NaN {'Be'} {'Long Moan - maybe'} 40472.77 NaN NaN NaN NaN NaN NaN NaN {'too weak' } NaN NaN
{'I:\DC\GofMX_DC02\GofMX_DC02_disks01-16_df100\GofMX_DC02_disks01-08_df100.ltsa'} NaN {'Be'} {'Long Moan - maybe'} 40472.92 NaN NaN NaN NaN NaN NaN NaN {'too weak' } NaN NaN
{'I:\DC\GofMX_DC02\GofMX_DC02_disks01-16_df100\GofMX_DC02_disks01-08_df100.ltsa'} NaN {'Be'} {'Long Moan - maybe'} 40473.85 NaN NaN NaN NaN NaN NaN NaN {'too weak' } NaN NaN
{'I:\DC\GofMX_DC02\GofMX_DC02_disks01-16_df100\GofMX_DC02_disks01-08_df100.ltsa'} NaN {'Be'} {'Long Moan - maybe'} 40474.18 NaN NaN NaN NaN NaN NaN NaN {'too weak' } NaN NaN
{'I:\DC\GofMX_DC02\GofMX_DC02_disks01-16_df100\GofMX_DC02_disks01-08_df100.ltsa'} NaN {'Be'} {'Long Moan - maybe'} 40474.48 NaN NaN NaN NaN NaN NaN NaN {'too weak' } NaN NaN
{'I:\DC\GofMX_DC02\GofMX_DC02_disks01-16_df100\GofMX_DC02_disks01-08_df100.ltsa'} NaN {'Be'} {'Long Moan - maybe'} 40474.49 NaN NaN NaN NaN NaN NaN NaN {'too weak' } NaN NaN
{'I:\DC\GofMX_DC02\GofMX_DC02_disks01-16_df100\GofMX_DC02_disks01-08_df100.ltsa'} NaN {'Be'} {'Long Moan - maybe'} 40481.65 NaN NaN NaN NaN NaN NaN NaN {'among ship noise'} NaN NaN
{'I:\DC\GofMX_DC02\GofMX_DC02_disks01-16_df100\GofMX_DC02_disks01-08_df100.ltsa'} NaN {'Be'} {'Long Moan - maybe'} 40481.65 NaN NaN NaN NaN NaN NaN NaN {'among ship noise'} NaN NaN
{'I:\DC\GofMX_DC02\GofMX_DC02_disks01-16_df100\GofMX_DC02_disks01-08_df100.ltsa'} NaN {'Be'} {'Long Moan - maybe'} 40481.97 NaN NaN NaN NaN NaN NaN NaN {'150 Hz tone only'} NaN NaN
{'I:\DC\GofMX_DC02\GofMX_DC02_disks01-16_df100\GofMX_DC02_disks01-08_df100.ltsa'} NaN {'Be'} {'Long Moan - maybe'} 40482.52 NaN NaN NaN NaN NaN NaN NaN {'too weak' } NaN NaN
{'I:\DC\GofMX_DC02\GofMX_DC02_disks01-16_df100\GofMX_DC02_disks01-08_df100.ltsa'} NaN {'Be'} {'Long Moan - maybe'} 40482.93 NaN NaN NaN NaN NaN NaN NaN {'too weak' } NaN NaN
{'I:\DC\GofMX_DC02\GofMX_DC02_disks01-16_df100\GofMX_DC02_disks01-08_df100.ltsa'} NaN {'Be'} {'Long Moan - maybe'} 40495.82 NaN NaN NaN NaN NaN NaN NaN {'150 Hz tone only'} NaN NaN
{'I:\DC\GofMX_DC02\GofMX_DC02_disks01-16_df100\GofMX_DC02_disks01-08_df100.ltsa'} NaN {'Be'} {'Long Moan - maybe'} 40496.62 NaN NaN NaN NaN NaN NaN NaN {'150 Hz tone only'} NaN NaN
{'I:\DC\GofMX_DC02\GofMX_DC02_disks01-16_df100\GofMX_DC02_disks01-08_df100.ltsa'} NaN {'Be'} {'Long Moan - maybe'} 40497.80 NaN NaN NaN NaN NaN NaN NaN {'too weak' } NaN NaN
{'I:\DC\GofMX_DC02\GofMX_DC02_disks01-16_df100\GofMX_DC02_disks01-08_df100.ltsa'} NaN {'Be'} {'Long Moan - maybe'} 40500.00 NaN NaN NaN NaN NaN NaN NaN {'too weak' } NaN NaN
{'I:\DC\GofMX_DC02\GofMX_DC02_disks01-16_df100\GofMX_DC02_disks01-08_df100.ltsa'} NaN {'Be'} {'Long Moan - maybe'} 40500.01 NaN NaN NaN NaN NaN NaN NaN {'too weak' } NaN NaN
{'I:\DC\GofMX_DC02\GofMX_DC02_disks01-16_df100\GofMX_DC02_disks01-08_df100.ltsa'} NaN {'Be'} {'Long Moan - maybe'} 40500.97 NaN NaN NaN NaN NaN NaN NaN {'150 Hz tone only'} NaN NaN
{'I:\DC\GofMX_DC02\GofMX_DC02_disks01-16_df100\GofMX_DC02_disks01-08_df100.ltsa'} NaN {'Be'} {'Long Moan - maybe'} 40501.28 NaN NaN NaN NaN NaN NaN NaN {'too weak' } NaN NaN
{'I:\DC\GofMX_DC02\GofMX_DC02_disks01-16_df100\GofMX_DC02_disks01-08_df100.ltsa'} NaN {'Be'} {'Long Moan - maybe'} 40501.51 NaN NaN NaN NaN NaN NaN NaN {'too weak' } NaN NaN
{'I:\DC\GofMX_DC02\GofMX_DC02_disks01-16_df100\GofMX_DC02_disks01-08_df100.ltsa'} NaN {'Be'} {'Long Moan - maybe'} 40503.05 NaN NaN NaN NaN NaN NaN NaN {'150 Hz tone only'} NaN NaN
{'I:\DC\GofMX_DC02\GofMX_DC02_disks01-16_df100\GofMX_DC02_disks01-08_df100.ltsa'} NaN {'Be'} {'Long Moan - maybe'} 40503.21 NaN NaN NaN NaN NaN NaN NaN {'too weak' } NaN NaN
{'I:\DC\GofMX_DC02\GofMX_DC02_disks01-16_df100\GofMX_DC02_disks01-08_df100.ltsa'} NaN {'Be'} {'Long Moan - maybe'} 40504.09 NaN NaN NaN NaN NaN NaN NaN {'too weak' } NaN NaN
{'I:\DC\GofMX_DC02\GofMX_DC02_disks01-16_df100\GofMX_DC02_disks01-08_df100.ltsa'} NaN {'Be'} {'Long Moan - maybe'} 40504.98 NaN NaN NaN NaN NaN NaN NaN {'too weak' } NaN NaN
{'I:\DC\GofMX_DC02\GofMX_DC02_disks01-16_df100\GofMX_DC02_disks01-08_df100.ltsa'} NaN {'Be'} {'Long Moan - maybe'} 40505.04 NaN NaN NaN NaN NaN NaN NaN {'150 Hz tone only'} NaN NaN
>> datetime(tDC.StartTime,'ConvertFrom','excel')
ans =
24×1 datetime array
21-Oct-2010 18:25:24
21-Oct-2010 22:11:18
22-Oct-2010 20:26:00
23-Oct-2010 04:21:04
23-Oct-2010 11:27:24
23-Oct-2010 11:39:12
30-Oct-2010 15:39:08
30-Oct-2010 15:39:52
30-Oct-2010 23:23:48
31-Oct-2010 12:28:48
31-Oct-2010 22:15:27
13-Nov-2010 19:42:07
14-Nov-2010 14:51:47
15-Nov-2010 19:14:35
18-Nov-2010 00:05:39
18-Nov-2010 00:15:25
18-Nov-2010 23:22:56
19-Nov-2010 06:42:32
19-Nov-2010 12:08:30
21-Nov-2010 01:19:02
21-Nov-2010 05:04:24
22-Nov-2010 02:11:54
22-Nov-2010 23:36:46
23-Nov-2010 00:50:41
>>
更多回答(0 个)
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Data Type Conversion 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!