Nested For Loop - Correction Needed

I am using Nested For Loop in a Macro to split each day in three durations (N.Peak from 00:00 to 11:00 hrs, Peak from 11:00 to 23:00 hrs, and N.Peak from 23:00 to 24:00 hrs), with the exception that Sunday has only one duration ( From Start time to End time if Given, or from 00:00 to 24:00 hrs if it comes in between two dates as full day)


I have large Number of such days and this macro is generating correct durations for each day other than Sunday.

For Sunday it is Generating duration from Start time to End time , and also from 00:00 to 24:00 hrs ( As a Full Day , which is not required )

following is the code. Can anyone correct it for me. I have also attached Excel Sheet with the Sample data and Macro


Nested For Loop.xlsm

Please Login or Register  to view this content.

i would appreciate if anybody can help me to correct this code.

No . it is not working properly

It is generating false output in start time on Sunday.

I have attached Excel File with your code for your kind refrence

Nested For Loop R1.xlsm

Thanks for taking time to help me

Many thanks for you time and kind reply

But it is not working properly

Pl see the attached file

Nested For Loop R2.xlsm

Can anyone help me

Ok

I have to split approx 100's of entries (From Start Date and Time to End Date and Time) to three duration in each day. (from 00:00 hrs to 11:00 hrs Duration is N.Peak, from 11:00 hrs to 23:00 duration is Peak, and from 23:00 to 24:00 hrs , duration is N.Peak)
But if day is Sunday, then duration is either "Sunday" from Start Time to End time, or "Sunday" from 00:00 to 24:00 hrs

Let us Start with examples

1st Entry

Sr. No. . . . . . . Start Date & Time. . . . . . . . . End Date&Time. . . . . . Event
1 . . . . . . . 03-March-13 01:15 . . . . . . . . . 05-March-13 10:55 . . . FO
2 . . . . . . . 03-March-13 13:00 . . . . . . . . . 11-March-13 23:55 . . . FO


Spliting Entry 1 . . . . . . . 03-March-13 01:15 . . . . . . . . . 05-March-13 10:55 . . . FO would be as follows

Sr. No. . . . Start Date . . . . . . Start Time . . . . . . . . End Time . . . . . . Duration . . . . . . Event

1 . . . . . .03-March-13. . . . . 01:15 . . . . . . . . . . . 02:00 . . . . . . . . Sunday . . . . . . . .FO (First it should complete the running hour, and as Day is Sunday, Duration should also be Sunday)
03-March-13. . . . . 02:00 . . . . . . . . . . . 24:00 . . . . . . . . Sunday . . . . . . . .FO (As Day is Sunday, so Duration is Sunday till the end of the day)
04-March-13. . . . . 00:00 . . . . . . . . . . . 11:00 . . . . . . . . N.Peak . . . . . . . .FO (As Day is not sunday, so Duration Criteria applies )
04-March-13. . . . . 11:00 . . . . . . . . . . . 23:00 . . . . . . . . Peak . . . . . . . .FO (As Day is not sunday, so Duration Criteria applies )
04-March-13. . . . . 23:00 . . . . . . . . . . . 24:00 . . . . . . . . N.Peak . . . . . . . .FO (As Day is not sunday, so Duration Criteria applies )
05-March-13. . . . . 00:00 . . . . . . . . . . . 10:00 . . . . . . . . Peak . . . . . . . .FO (breaks the time , as minutes are not Zero )
05-March-13. . . . . 10:00 . . . . . . . . . . . 10:55 . . . . . . . . Peak . . . . . . . .FO (Completes the time to end time)

In the same way i have to split Entry 2 . . . . . . . 03-March-13 13:00 . . . . . . . . . 11-March-13 22:55 . . . FO as

Sr. No. . . . Start Date . . . . . . Start Time . . . . . . . . End Time . . . . . . Duration . . . . . . Event

1 . . . . . .03-March-13. . . . . 13:00 . . . . . . . . . . . 24:00 . . . . . . . . Sunday . . . . . . . .FO (No need to complete the running hour as minutes are Zero, and as Day is Sunday, Duration should also be Sunday till the end of the day)
04-March-13. . . . . 00:00 . . . . . . . . . . . 11:00 . . . . . . . . N.Peak . . . . . . . .FO (As Day is not sunday, so Duration Criteria applies )
04-March-13. . . . . 11:00 . . . . . . . . . . . 23:00 . . . . . . . . Peak . . . . . . . .FO (As Day is not sunday, so Duration Criteria applies )
04-March-13. . . . . 23:00 . . . . . . . . . . . 24:00 . . . . . . . . N.Peak . . . . . . . .FO (As Day is not sunday, so Duration Criteria applies )
05-March-13. . . . . 00:00 . . . . . . . . . . . 11:00 . . . . . . . . N.Peak . . . . . . . .FO (As Day is not sunday, so Duration Criteria applies )
05-March-13. . . . . 11:00 . . . . . . . . . . . 23:00 . . . . . . . . Peak . . . . . . . .FO (As Day is not sunday, so Duration Criteria applies )
05-March-13. . . . . 23:00 . . . . . . . . . . . 24:00 . . . . . . . . N.Peak . . . . . . . .FO (As Day is not sunday, so Duration Criteria applies )
06-March-13. . . . . 00:00 . . . . . . . . . . . 11:00 . . . . . . . . N.Peak . . . . . . . .FO (As Day is not sunday, so Duration Criteria applies )
06-March-13. . . . . 11:00 . . . . . . . . . . . 23:00 . . . . . . . . Peak . . . . . . . .FO (As Day is not sunday, so Duration Criteria applies )
06-March-13. . . . . 23:00 . . . . . . . . . . . 24:00 . . . . . . . . N.Peak . . . . . . . .FO (As Day is not sunday, so Duration Criteria applies )
07-March-13. . . . . 00:00 . . . . . . . . . . . 11:00 . . . . . . . . N.Peak . . . . . . . .FO (As Day is not sunday, so Duration Criteria applies )
07-March-13. . . . . 11:00 . . . . . . . . . . . 23:00 . . . . . . . . Peak . . . . . . . .FO (As Day is not sunday, so Duration Criteria applies )
07-March-13. . . . . 23:00 . . . . . . . . . . . 24:00 . . . . . . . . N.Peak . . . . . . . .FO (As Day is not sunday, so Duration Criteria applies )
08-March-13. . . . . 00:00 . . . . . . . . . . . 11:00 . . . . . . . . N.Peak . . . . . . . .FO (As Day is not sunday, so Duration Criteria applies )
08-March-13. . . . . 11:00 . . . . . . . . . . . 23:00 . . . . . . . . Peak . . . . . . . .FO (As Day is not sunday, so Duration Criteria applies )
08-March-13. . . . . 23:00 . . . . . . . . . . . 24:00 . . . . . . . . N.Peak . . . . . . . .FO (As Day is not sunday, so Duration Criteria applies )
09-March-13. . . . . 00:00 . . . . . . . . . . . 11:00 . . . . . . . . N.Peak . . . . . . . .FO (As Day is not sunday, so Duration Criteria applies )
09-March-13. . . . . 11:00 . . . . . . . . . . . 23:00 . . . . . . . . Peak . . . . . . . .FO (As Day is not sunday, so Duration Criteria applies )
09-March-13. . . . . 23:00 . . . . . . . . . . . 24:00 . . . . . . . . N.Peak . . . . . . . .FO (As Day is not sunday, so Duration Criteria applies )
10-March-13. . . . . 00:00 . . . . . . . . . . . 24:00 . . . . . . . . N.Peak . . . . . . . .FO (As Day is sunday, so Duration Criteria does not applies, Duration Criteria is Sunday )
11-March-13. . . . . 00:00 . . . . . . . . . . . 11:00 . . . . . . . . N.Peak . . . . . . . .FO (As Day is not sunday, so Duration Criteria applies )
11-March-13. . . . . 11:00 . . . . . . . . . . . 22:00 . . . . . . . . Peak . . . . . . . .FO (As Day is not sunday, so Duration Criteria applies , breaks the time to starting hour as minutes are not zero)
11-March-13. . . . . 22:00 . . . . . . . . . . . 22:55. . . . . . . . Peak . . . . . . . .FO (As Day is not sunday, so Duration Criteria applies , Completes the time )

You are realy a kind person
Following is the sample data
FO PFO Record.xlsm

JapanDave-I hope you must be working on it

No answer from there too yet. . . . .

Can you please re-do the code for me

Hi JapanDave

Many thanks for your patience , although i did wrong by starting another thread for same problem.
watersev is realy a good coder , and his code is working fine for me.

I am marking this thread as SOLVED

http://www.excelforum.com/showthread...t=#post3212924

You guys are really very kind.

Kind regards