Fill Column Based on Actual Time

[haiders7]

.....I have taken a look…......

Hi

Thanks again for all your effort.

I explain you in more detail now that what actually Sheet1 and Sheet2 are.

Sheet1: It is the plan sheet where the spots are place on a particular channel on specific date and specific time. Specific Time (Start Time) is the automated time generated from some other source. The New Time is the time we actual want from the historic data which is placed in Sheet2.

Sheet2: It has the historic data of all spots that were aired on different channels. So whenever the new plan is going to happen we have to pick an actual Ad time of the Ad from the historic data. Lets assume we have the spots plan in Sample Sheet and then we have output mentioned in Sample Sheet. In the output you will see that all New Time comes from the Sheet2 Data. It could be done through Random matching or some other logic but point is here that we have to pick the actual time that is available in Sheet2 Data.

MidBreak Start Time and End Time columns are for your info. If you want to use those columns for your logic then you can also use.


I have attached the zip file (Data Sheet v2.zip) so you can see my sample sheet as well.

One more thing to add, if in any case there is no spot aired in Sheet2 on a specific channel, date and timeslot then New Time will be same as start time.
In Sample sheet, I planned 8 spots (maximum number of spots plan in an hour) which will help you in designing a generic logic.

Feel free to any in case of any ambiguity.

Thanks
Haider










Basically, the output I am expecting is