How to convert 'DDMMYYYYhhmm' into 'DD-MM-YYYY hh:mm'

13 views (last 30 days)
Hi there,
I have some data (Please see attached the screenshot) in an Excel sheet. I need to convert it into timeseries with two coloumns. Basically, the first coloumn should the date and the second coloumn should be the values. I was wondering if anyone could help me with this.
The data is formatted in two columns separated by a single space:
The first column is the datetime in the following format: 'DDMMYYYYhhmm'. 'DD' is the day of the month as a two digit integer 'MM' is the month of the year as a two digit integer 'YYYY' is the year as a four digit integer 'hh' is the hour of the day (using 24 hour time) as a two digit integer 'mm' is the minute of the hour as a two digit integer.
The second column is the water level reading in metres in the following format: 'si.fff'. 's' is a prefix indicating if the reading is positive or negative positive readings are denoted by a space ' ' negative readings are denoted by a dash '-'. 'i.fff' is the height of the recording in metres
For example, the line '190120380314 3.142' would refer to a height of 3.142 metres recorded at 03:14am on the 19th of January 2038.
Thank you.
Kind regards,
Ali
  2 Comments
Stephen23
Stephen23 on 19 Jan 2021
@Ali Saremi: please upload the original data file (not a screenshot) by clicking the paperclip button.
Ali Saremi
Ali Saremi on 19 Jan 2021
@Stephen Cobeldick: Thank you for your reply. I have already uploaded the orginal data file. Look forward to hearing from you.

Sign in to comment.

Accepted Answer

Bjorn Gustavsson
Bjorn Gustavsson on 19 Jan 2021
Edited: Bjorn Gustavsson on 19 Jan 2021
You can do something like this to convert a date-string to another date-string:
datestr(datenum(char(['190120211029';'140219671940']),'ddmmyyyyHHMM'),'yyyy-mm-dd HH:MM')
You can of course replace '190120211029' with the string/char-array with your time instances.
HTH
  7 Comments
Bjorn Gustavsson
Bjorn Gustavsson on 20 Jan 2021
Reading the help and documentation I learned that you can do something like this:
MeasurementTime = datetime(str2num(C_1(:,5:8)),... % Extract year
str2num(C_1(:,3:4)),... % month
str2num(C_1(:,1:2)),... % etc
str2num(C_1(:,9:10)),...
str2num(C_1(:,11:12)),...
zeros(size(C_1(:,11))));

Sign in to comment.

More Answers (0)

Categories

Find more on MATLAB in Help Center and File Exchange

Community Treasure Hunt

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

Start Hunting!