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

5 visualizaciones (últimos 30 días)
Ashley Cook
Ashley Cook el 11 de En. de 2023
Comentada: Ashley Cook el 11 de En. de 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 comentarios
Mostrar 1 comentario más antiguo
dpb
dpb el 11 de En. de 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...

Iniciar sesión para comentar.

Iniciar sesión para responder a esta pregunta.

Respuesta aceptada

dpb
dpb el 11 de En. de 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
>>

Más respuestas (0)

Categorías

Más información sobre Data Type Identification en Help Center y File Exchange.

Etiquetas

Productos


Versión

R2022b

Community Treasure Hunt

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

Start Hunting!

Translated by