+ Reply to Thread
Results 1 to 5 of 5

Split Time Duration to first complete the running hour and then go to End time

  1. #1
    Registered User
    Join Date
    07-29-2010
    Location
    Multan
    MS-Off Ver
    Excel 2010
    Posts
    38

    Split Time Duration to first complete the running hour and then go to End time

    I have a sheet with large data in which I have to sort duration of unit status on following rules

    Rule 1: If minutes in start time & End Time are Zero, then no need to split them
    e.g 03 Jan 2013 09:00 to 04 Jan 2013 22:00

    Rule 2: If minutes in start time are not Zero, and End time is in same hour, then also no need to change that duration.
    e.g 12 Jan 2013 09:10 to 12 Jan 2013 09:20

    Rule 3: if Minutes in start time are not zero and end time is in another hour with Zero minutes, then split the duration as
    First complete the start hour to Zero Minutes of next hour, and then go to End time

    e.g. 12 Jan 2013 09:20 to 12 Jan 2013 12:00

    This should be changed as
    12 Jan 2013 09:20 to 12 Jan 2013 10:00
    12 Jan 2013 10:00 to 12 Jan 2013 12:00

    Rule 4: If minutes in Start and End time both are not zero, then split the duration as
    First complete the start hour to Zero Minutes of next hour, and then go to End time till Zero minutes of Ending Hours, and then finally complete the last has as separate duration

    e.g. 12 Jan 2013 13:29 to 12 Jan 2013 19:05

    This should be changed as
    12 Jan 2013 13:29 to 12 Jan 2013 14:00
    12 Jan 2013 14:00 to 12 Jan 2013 19:00
    12 Jan 2013 19:00 to 12 Jan 2013 19:05

    Excel Sheet having Sample data is attached
    Attached Files Attached Files
    Last edited by joogibabu; 04-30-2013 at 04:58 AM.

  2. #2
    Registered User
    Join Date
    07-29-2010
    Location
    Multan
    MS-Off Ver
    Excel 2010
    Posts
    38

    Re: Split Time Duration to first complete the running hour and then go to End time

    Can this be done with macro , or i have to do it manulay ?
    i would realy appreciate , if anyone can help me

  3. #3
    Registered User
    Join Date
    07-29-2010
    Location
    Multan
    MS-Off Ver
    Excel 2010
    Posts
    38

    Re: Split Time Duration to first complete the running hour and then go to End time

    Can anyone code it for me ?

  4. #4
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Split Time Duration to first complete the running hour and then go to End time

    hi joogibabu, option, please check attachment and press Run button on Sheet1

    PS. If start minutes are zero and end minutes are not, and hours are different for now the code will consider this as Rule 4. This option is not covered by your rules.
    Example: 01/Jan/13 04:00 to 05/Jan/13 09:20
    Result for now:
    01/Jan/13 04:00 to 01/Jan/13 05:00
    01/Jan/13 05:00 to 05/Jan/13 09:00
    05/Jan/13 09:00 to 05/Jan/13 09:20
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    07-29-2010
    Location
    Multan
    MS-Off Ver
    Excel 2010
    Posts
    38

    Re: Split Time Duration to first complete the running hour and then go to End time

    Many Thanks Watersev

    You have been so kind to me. . . The Code is working perfectly . . . You are really a genius. . . Although i forget to mention If start minutes are zero and end minutes are not, and hours are different but you covered it .

    Many thanks

    kind regards

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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