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
Bookmarks