Split a excel sheet to separate excel files based on date and time column

Hi,

I have a master excel file contains data including date and time column, I want to break this sheet in to multiple files which would contains the data between specific times. For example i want the first sheet with the data between 7/4/2016 0:00 & 7/4/2016 0:30 and the next should be 7/4/2016 0:31 & 7/4/2016 01:00 and so on. please note that the time is in 24hrs format. I have attached the file for the reference.

It would it be greatly appreciated if there is a simple solution for this, because i dont have a quite knowledge in VBA!

Thanks,

Hi,

See the attached file.

I have inserted a new column to split the time into different brackets with the help of LOOKUP function.

=LOOKUP((C2-INT(C2)),U$2:U$97,V$2:V$97)

If you need new files to be created for each of these time groups, you would need VBA for that.

Perhaps a macro like this is what you are looking for?

Before you can run this macro you must add a folder named "C:\Myfiles" or you can change this line

ActiveWorkbook.SaveAs Filename:="C:\Myfiles\" & ActiveSheet.Name & "_" & i, FileFormat:=51

in the macro to save the created files to a folder of your choice.

To test run macro "MakeFiles"

Macro starts by making a copy of sheet1 and renames this to "data", then adds a sheet named "result"

Then macro sets an autofilter on sheet "data" and copies the filtered result to sheet "result". Macro then makes a file named "result_1.xlsx" saves this to folder "C:\Myfiles" and closes this file and clears sheet "result", activates sheet "data" and deletes the autofilter result.

Then a new autofilter is set in sheet "data" and result copied and file "result_2.xlsx" is created .......

As your data in column C not only contains year-month-day and hh and mm but also ss i.e.
value in C2 looks to be 2016-07-04 00:00 but in the formula bar is shows up as 2016-07-04 00:00:11 and the seconds make a small error in the filtering for every 30 minute set by the autofilter.

Alf