Half hourly data for three years - break down by time period

Hi Tim,

There are two ways it

First -

Sort by Column A this will sort your data as per time then you can shift it to further column like D,E,F & G

Second -

Insert 4 column after column A, then copy & paste column A to column B, then Select Column B go to Data Tab - Text to column function -select delimited - click on space - finish

this will separate date & time then your data will be in column E hence put below formulas in receptive columns

In column G - Cell G17

=IF(C17>=TIME(0,0,0),IF(C17<=TIME(6,0,0),E17,""))

In column H - Cell H 17

=IF(C17>=TIME(6,0,1),IF(C17<=TIME(12,0,0),E17,""))

In column I - Cell I 17

=IF(C17>=TIME(12,0,1),IF(C17<=TIME(20,0,0),E17,""))

In column J - Cell J17

=IF(C17>=TIME(20,0,1),IF(C17<=TIME(23,59,59),E17,""))


then Paste same formula to all rows , & do paste special to values

then delete column B, C, D your data will be ready


That's the only solution i have found , hope this will work for you

Thanks,
A