Creating a datetime variable - unconventionally

I have an excel sheet which has several columns including a date column of which I would like to create a datetime object from. The snippet of this data is as shown below.

I would like to create a datetime object such that shows the year and the month alone ( in this case 2003-January) with 48 intervals for each month - for the whole range of the year. It is more of replicating the 2003-January datetime 48 times and then moving next to 2003-February till when we reach December. Any help will be appreciated.

Answer 1

[Star Strider]

Using months instead of minutes —

Date = {'200301'; '200306'; '200312'}

Date = 3×1 cell array
    {'200301'}
    {'200306'}
    {'200312'}

DateDT = datetime(Date, 'InputFormat','yyyyMM')

DateDT = 3×1 datetime array
   01-Jan-2003
   01-Jun-2003
   01-Dec-2003

DateDT.Format = 'yyyyMM'

DateDT = 3×1 datetime array
   200301
   200306
   200312

.

[Lewis Waswa]

Just a quick question on this one, is it not possible to simply obtain "Jan-2003" alone without the first or the second? This method works well if I convert the number to string and then to a datetime object thereafter. However, there is a recurring generation of NaT for reasons I can't tell. Please do have a look at the file attached. The December data is represented as NaT. Any further clarification?

[Star Strider]

Yes. Just change the Format string.

Date = {'200301'; '200306'; '200312'}

Date = 3×1 cell array
    {'200301'}
    {'200306'}
    {'200312'}

DateDT = datetime(Date, 'InputFormat','yyyyMM')

DateDT = 3×1 datetime array
   01-Jan-2003
   01-Jun-2003
   01-Dec-2003

DateDT.Format = 'MMM-yyyy'

DateDT = 3×1 datetime array
   Jan-2003
   Jun-2003
   Dec-2003

EDIT — (17 Mar 2022 at 14:12)

The problem is that beginning at row 576 the ‘month’ switches from 12 to 13. and checking the last two digits of the ‘Date’ variable shows them to actually be days of the month rather than months —

format longg
T1 = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/930604/Data-E.xlsx', 'VariableNamingRule','preserve');

CheckTable = T1(570:580,:)

CheckTable = 11×10 table
     Date     Hour    Increment     Unit      KW     Var6    Kvar    Var8          KVA                  Pf        
    ______    ____    _________    ______    ____    ____    ____    ____    ________________    _________________

    200312    2100       30        {'KW'}    6581     0      2036     0      6888.74858011236    0.955325909120734
    200312    2130       30        {'KW'}    5724     0      1863     0      6019.54690985958    0.950902133618148
    200312    2200       30        {'KW'}    5119     0      1874     0      5451.24178513483    0.939052091572817
    200312    2230       30        {'KW'}    4464     0      1923     0      4860.57866925328    0.918409165607805
    200312    2300       30        {'KW'}    4041     0      1864     0      4450.18842297717    0.908051438706629
    200312    2330       30        {'KW'}    3766     0      1839     0      4191.02338337547    0.898587207825799
    200312    2400       30        {'KW'}    3624     0      1752     0      4025.28011447651    0.900310014939496
    200313      30       30        {'KW'}    3458     0      1732     0      3867.50410471663    0.894116698100664
    200313     100       30        {'KW'}    3378     0      1741     0       3800.2585438362    0.888886890466683
    200313     130       30        {'KW'}    3390     0      1724     0      3803.19286915613    0.891356319973379
    200313     200       30        {'KW'}    3326     0      1670     0       3721.7168081411    0.893673584385709

CheckDate = [min(rem(T1{:,1},100)); max(rem(T1{:,1},100))]      % Last Two Digits Of 'Date'

CheckDate = 2×1

1 31

I have no idea what the ‘Date’ numbers actually represent, however the last two digits are not months. I have no idea where the month designations would be, since I don’t see anything in the table that corresponds to them.

.

[Lewis Waswa]

Thank you @Star Strider. Any hints as to why I am still getting NaT?

[Star Strider]

Yes.

See my edited comment above.

EDIT — (17 Mar 2022 at 14:40)

The last two digits appear to be days of the month, not months, so that is the reason for the NaT results. There do not appear to be any month data in the file, so I am not certain that defining the months will be possible.

It took me a few minutes to determine that.

EDIT — (17 Mar 2022 at 16:34)

I finally figured out how to parse this file.

Try this —

format longg                                                                        % Optional
T1 = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/930604/Data-E.xlsx', 'VariableNamingRule','preserve');
T1.Hour(T1.Hour==2400) = 0;                                                         % Set '2400' To '0000'
Hourdt = datetime(compose('%04d',T1.Hour),'InputFormat','HHmm', 'Format','HH:mm');  % Convert 'Hour' To 'datetime'
Datedt = datetime(string(T1.Date),'InputFormat',"yyyydd");                          % Parse 'Date' Correctly
T1.Date = Datedt+timeofday(Hourdt);                                                 % Combine Them
T1 = removevars(T1,{'Hour'});                                                       % 'Hour' Is No Longer Necessary
T1.Date.Format = 'yyyy-dd HH:mm'                                                    % Define Fromat & Display Result

T1 = 9362×9 table
        Date         Increment     Unit      KW     Var6    Kvar    Var8          KVA                  Pf        
    _____________    _________    ______    ____    ____    ____    ____    ________________    _________________

    2003-01 00:30       30        {'KW'}    3355     0      1749     0      3783.52031843361    0.886740315270456
    2003-01 01:00       30        {'KW'}    3227     0      1682     0      3639.04561664181    0.886770966882779
    2003-01 01:30       30        {'KW'}    3240     0      1714     0      3665.43258020114    0.883933868406387
    2003-01 02:00       30        {'KW'}    3147     0      1676     0      3565.47121710441    0.882632282909225
    2003-01 02:30       30        {'KW'}    3120     0      1535     0       3477.1576035607    0.897284608786509
    2003-01 03:00       30        {'KW'}    3099     0      1495     0      3440.75950917817    0.900673235584605
    2003-01 03:30       30        {'KW'}    3083     0      1465     0      3413.37281878203    0.903212207888877
    2003-01 04:00       30        {'KW'}    3111     0      1452     0       3433.1654489698     0.90616081463057
    2003-01 04:30       30        {'KW'}    3249     0      1492     0       3575.2013929288    0.908759995010639
    2003-01 05:00       30        {'KW'}    3325     0      1466     0      3633.83832881982    0.915010437759315
    2003-01 05:30       30        {'KW'}    3547     0      1484     0      3844.92717746383    0.922514221020865
    2003-01 06:00       30        {'KW'}    3843     0      1492     0      4122.46443283626    0.932209376845008
    2003-01 06:30       30        {'KW'}    4204     0      1675     0       4525.3995403721    0.928978748173543
    2003-01 07:00       30        {'KW'}    4575     0      1721     0      4887.99202127008    0.935967157902857
    2003-01 07:30       30        {'KW'}    5116     0      1832     0       5434.1218241773    0.941458466617012
    2003-01 08:00       30        {'KW'}    5570     0      1937     0      5897.19161974579    0.944517383723764

% TT1 = table2timetable(T1);                                                          % Optional

There are a couple missing values at the end. The rest are read and parsed correctly.

.

[Lewis Waswa]

Thank you @Star Strider. This works well.

[Star Strider]

As always, my pleasure!

Answer 2

[Arif Hoq]

As you did not attach your data. let's try with your first data

A='200301';
date=datetime(A,'InputFormat','yyyymm');
date2=datetime(date,'Format','yyyy-mm')

date2 = datetime
   2003-01

then every 48 interval try this

T.Date(1:48:end,:)  % if T is your table

[Lewis Waswa]

Thank you @Arif Hoq.

Answer 3

[Arif Hoq]

try this:

B=readtable('Data-E.xlsx');
C=B(1:48:end,:);
dat=string(table2cell(C(:,1)));
date = datetime(dat, 'InputFormat','yyyyMM');
date2=datetime(date,'Format','MMM-yyyy');
date3=rmmissing(date2);

[Arif Hoq]

or try this:

B=readtable('Data-E.xlsx');
C=B(1:48:end,:);
dat=string(table2cell(C(:,1)));
date = datetime(dat, 'InputFormat','yyyyMM');
date2=datetime(date,'Format','MMM-yyyy');
% [R,TF]=rmmissing(date2)
D=cellstr(date2)
[D Lia Lib]=unique(D,'rows','stable')
E=C(Lia,:)
E(13,:)=[] % deleting Nat value
value=string(table2cell(E(:,2:end)));
dat2=string(table2cell(E(:,1)));
date3 = datetime(dat2, 'InputFormat','yyyyMM');
date4=datetime(date3,'Format','MMM-yyyy');
T=timetable(date4,value) % final array