Clear Filters
Clear Filters

When converting datenum to datetime, days are off by 1 and years are incorrect

9 views (last 30 days)
Ashley Cook
Ashley Cook on 11 Jan 2023
Commented: Ashley Cook on 11 Jan 2023
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
%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 Comments
Show 1 older comment
dpb
dpb on 11 Jan 2023
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...

Sign in to comment.

Sign in to answer this question.

Accepted Answer

dpb
dpb on 11 Jan 2023
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
>>

More Answers (0)

Categories

Find more on Data Type Conversion in Help Center and File Exchange

Tags

Products


Release

R2022b

Community Treasure Hunt

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

Start Hunting!