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

**machds42** · 07-06-2013, 08:18 PM

Good day,

I have every half hour of every day for three years in Column A (eg 1/01/2009 0:30, through to today 07/07/2013 10:00), and numerical data in column B.

I need to create four time periods in four different columns (D-G):
12.00am - 6:00am (column D)
6:00am - 12:00pm (column E)
12:00pm - 8:00pm (column F)
8:00pm - 12:00am (column G)

In those columns, if the time in column A, falls within the time period listed above (e.g. the date is 03/04/2009 4:00am, therefore falls within the time period in Column D), then I need to return all of the data points listed in the column for that time period. Every half hour of every day for 3 + years equates to 79,000 data points across those four columns.

I just cant get it right. Would appreciate anyones assistance. I have some very short deadlines on a work project.

Cheers

Tim

**BadlySpelledBuoy** · 07-07-2013, 09:53 AM

Tim,

Could you post a small sample of the data (in a workbook) and show manually your expected outcome?

**amy_d2** · 07-07-2013, 03:03 PM

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

**FDibbins** · 07-07-2013, 03:32 PM

Hi and welcome to the forum

You didnt say which column you wanted te data returned from, so, assuming it was B, use these, copied down...

D1=if(AND(MOD($A1,1)>=0,MOD($A1,1)<=0.25),B1,"")
E1=if(AND(MOD($A1,1)>0.75,MOD($A1,1)<=1),B1,"")
F1=if(AND(MOD($A1,1)>0.5,MOD($A1,1)<=0.75),B1,"")
G1=if(AND(MOD($A1,1)>0.25,MOD($A1,1)<=0.5),B1,"")

**machds42** · 07-07-2013, 08:06 PM

Great - I was able to adapt that MOD formula and it worked well.

Many thanks

**FDibbins** · 07-07-2013, 08:16 PM

you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below)