+ Reply to Thread
Results 1 to 3 of 3

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

  1. #1
    Registered User
    Join Date
    05-02-2016
    Location
    New Delhi
    MS-Off Ver
    2013
    Posts
    3

    Smile 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,
    Attached Files Attached Files

  2. #2
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,134

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

    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.
    Attached Files Attached Files

  3. #3
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,593

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

    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
    Please Login or Register  to view this content.
    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
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Split Excel Sheet Data into mutliple files based on two criteria
    By saleembasha in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-22-2016, 12:14 PM
  2. Split the data in new excel Files based on Column
    By sai0449 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-10-2015, 11:18 AM
  3. [SOLVED] How to split Excel into different files based on a column?
    By stan255 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-10-2015, 12:57 AM
  4. Replies: 3
    Last Post: 06-23-2015, 03:58 PM
  5. Split the excel data in worksheet to save it in two separate excel files
    By sat1591 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-20-2014, 04:52 AM
  6. Split Excel into Multiple csv files based upon the sum of column count.
    By sukanya123 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-02-2013, 07:28 PM
  7. [SOLVED] How To Split Excel File Into Separate Workbooks Based on Values In a Specific Column
    By UzieJacuzzi in forum Excel Programming / VBA / Macros
    Replies: 30
    Last Post: 07-31-2012, 07:26 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1