Discover MakerZone

MATLAB and Simulink resources for Arduino, LEGO, and Raspberry Pi

Learn more

Discover what MATLAB® can do for your career.

Opportunities for recent engineering grads.

Apply Today

Thread Subject:
"Failed on converting date string to date number"

Subject: "Failed on converting date string to date number"

From: Mikey Evans

Date: 13 May, 2013 11:17:07

Message: 1 of 5

Hello!

I have an excel file with dates and time in the format

4.1.2012 06:00
4.1.2012 07:00
4.1.2012 08:00
4.1.2012 09:00

All in the same cell.

Imported it into Matlab using [Data, DatesTime] = xlsread('Datafile.xlsx');
and now I'd like to plot the Data versus the DatesTime.

I tried to convert DatesTime using

DateNumber = datenum(DatesTime, 'dd-mmm-yyyy HH:MM:SS')

and then (as I didn't have any seconds in my file)

DateNumber = datenum(DatesTime, 'dd-mmm-yyyy HH:MM')

but both gave me the error: "Failed on converting date string to date number"

I then tried converting the example given at mathworks page http://www.mathworks.se/support/solutions/en/data/1-6O7A2E/index.html?product=ML

DN = datenum('21-Dez-2007 09:30:20', 'dd-mmm-yyyy HH:MM:SS');
dStr = datestr(DN, 31, 'local')

and just

DN = datenum('21-Dez-2007 09:30:20', 'dd-mmm-yyyy HH:MM:SS');

or

DN = datenum('21-12-2007 09:30:20', 'dd-mm-yyyy HH:MM:SS');

but they all give me the same error.

How should I convert the string?

Subject: "Failed on converting date string to date number"

From: Barry Williams

Date: 14 May, 2013 14:43:14

Message: 2 of 5

"Mikey Evans" wrote in message <kmqi3j$jnl$1@newscl01ah.mathworks.com>...
> Hello!
>
> I have an excel file with dates and time in the format
>
> 4.1.2012 06:00
> 4.1.2012 07:00
> 4.1.2012 08:00
> 4.1.2012 09:00
>
> All in the same cell.
>
> Imported it into Matlab using [Data, DatesTime] = xlsread('Datafile.xlsx');
> and now I'd like to plot the Data versus the DatesTime.
>
> I tried to convert DatesTime using
>
> DateNumber = datenum(DatesTime, 'dd-mmm-yyyy HH:MM:SS')
>
> and then (as I didn't have any seconds in my file)
>
> DateNumber = datenum(DatesTime, 'dd-mmm-yyyy HH:MM')
>
> but both gave me the error: "Failed on converting date string to date number"
>
> I then tried converting the example given at mathworks page http://www.mathworks.se/support/solutions/en/data/1-6O7A2E/index.html?product=ML
>
> DN = datenum('21-Dez-2007 09:30:20', 'dd-mmm-yyyy HH:MM:SS');
> dStr = datestr(DN, 31, 'local')
>
> and just
>
> DN = datenum('21-Dez-2007 09:30:20', 'dd-mmm-yyyy HH:MM:SS');
>
> or
>
> DN = datenum('21-12-2007 09:30:20', 'dd-mm-yyyy HH:MM:SS');
>
> but they all give me the same error.
>
> How should I convert the string?

Use successive iterations of strtok to parse the cell into dates and times, concatenate them into date/time strings, and then convert them to datenumbers:

dn = datenum(dateTimeStr, 'dd.mm.yyyy HH:MM');

Barry

Subject: "Failed on converting date string to date number"

From: dpb

Date: 14 May, 2013 16:59:43

Message: 3 of 5

On 5/14/2013 9:43 AM, Barry Williams wrote:
> "Mikey Evans" wrote in message <kmqi3j$jnl$1@newscl01ah.mathworks.com>...
...
>> I have an excel file with dates and time in the format
>>
>> 4.1.2012 06:00
...

>> I tried to convert DatesTime using
>> DateNumber = datenum(DatesTime, 'dd-mmm-yyyy HH:MM:SS')
>>
>> and then (as I didn't have any seconds in my file)
>>
>> DateNumber = datenum(DatesTime, 'dd-mmm-yyyy HH:MM')
>>
>> but both gave me the error: "Failed on converting date string to date
>> number"
>>
>> I then tried converting the example given at mathworks page
...
>> DN = datenum('21-Dez-2007 09:30:20', 'dd-mmm-yyyy HH:MM:SS');
...

>> but they all give me the same error.
>>
>> How should I convert the string?
>
> Use successive iterations of strtok to parse the cell into dates and
> times, concatenate them into date/time strings, and then convert them to
> datenumbers:
>
> dn = datenum(dateTimeStr, 'dd.mm.yyyy HH:MM');

If the example doesn't work then there's something wrong w/ the
installation or somesuch...

 >> DN = datenum('21-12-2007 09:30:20', 'dd-mm-yyyy HH:MM:SS')
DN =
    7.3340e+05

For the input string of the original; just use the right format string
that matches the data--

 >> DN = datenum('4.1.2012 06:00', 'dd.mm.yyyy HH:MM')
DN =
    7.3487e+05
 >>

OP is trying to match '-' where has '.' in the field but the indication
that even the sample doesn't work according to him means he's fouled
something else up somehow.

OP--type

which datenum

at command line and report...or

clear datenum

and try again.

--

Subject: "Failed on converting date string to date number"

From: Barry Williams

Date: 14 May, 2013 19:20:09

Message: 4 of 5

dpb <none@non.net> wrote in message <kmtqi2$3g8$1@speranza.aioe.org>...
> On 5/14/2013 9:43 AM, Barry Williams wrote:
> > "Mikey Evans" wrote in message <kmqi3j$jnl$1@newscl01ah.mathworks.com>...
> ...
> >> I have an excel file with dates and time in the format
> >>
> >> 4.1.2012 06:00
> ...
>
> >> I tried to convert DatesTime using
> >> DateNumber = datenum(DatesTime, 'dd-mmm-yyyy HH:MM:SS')
> >>
> >> and then (as I didn't have any seconds in my file)
> >>
> >> DateNumber = datenum(DatesTime, 'dd-mmm-yyyy HH:MM')
> >>
> >> but both gave me the error: "Failed on converting date string to date
> >> number"
> >>
> >> I then tried converting the example given at mathworks page
> ...
> >> DN = datenum('21-Dez-2007 09:30:20', 'dd-mmm-yyyy HH:MM:SS');
> ...
>
> >> but they all give me the same error.
> >>
> >> How should I convert the string?
> >
> > Use successive iterations of strtok to parse the cell into dates and
> > times, concatenate them into date/time strings, and then convert them to
> > datenumbers:
> >
> > dn = datenum(dateTimeStr, 'dd.mm.yyyy HH:MM');
>
> If the example doesn't work then there's something wrong w/ the
> installation or somesuch...
>
> >> DN = datenum('21-12-2007 09:30:20', 'dd-mm-yyyy HH:MM:SS')
> DN =
> 7.3340e+05
>
> For the input string of the original; just use the right format string
> that matches the data--
>
> >> DN = datenum('4.1.2012 06:00', 'dd.mm.yyyy HH:MM')
> DN =
> 7.3487e+05
> >>
>
> OP is trying to match '-' where has '.' in the field but the indication
> that even the sample doesn't work according to him means he's fouled
> something else up somehow.
>
> OP--type
>
> which datenum
>
> at command line and report...or
>
> clear datenum
>
> and try again.
>
> --

He's got two problems: the format string and the fact that all of four of the date/time strings are apparently in a single cell.
Barry

Subject: "Failed on converting date string to date number"

From: dpb

Date: 15 May, 2013 00:31:45

Message: 5 of 5

On 5/14/2013 2:20 PM, Barry Williams wrote:
...

> He's got two problems: the format string and the fact that all of four
> of the date/time strings are apparently in a single cell.

That doesn't explain why it doesn't succeed on the sample, tho...

And, while I've not used it in anger, doc for XLSREAD indicates the
second return value is a cell array so I'd not think that description
was accurate but a fignewton of newbie confusion more likely.

So, anyways, not having used it I pasted the OPs date strings into an
excel workbook and I get...

 >> [~,dn]=xlsread('book1.xls')
dn =
     '4.1.2012 06:00'
     '4.1.2012 07:00'
     '4.1.2012 08:00'
     '4.1.2012 09:00'
 >> datenum(dn,'mm.dd.yyyy HH:MM')
ans =
    1.0e+05 *
     7.3496
     7.3496
     7.3496
     7.3496
 >>

Now, if I tried to dereference the cell in any way, then I get the OPs
error message...

--



--

Tags for this Thread

No tags are associated with this thread.

What are tags?

A tag is like a keyword or category label associated with each thread. Tags make it easier for you to find threads of interest.

Anyone can tag a thread. Tags are public and visible to everyone.

Contact us