Date challenge - transform effective date range to day time series

Hi there,
I have a date challenge, as attached for information. Date challenge - transform to time series.xlsx

I’d taken a stab at this separately, breaking it down into several steps but even then, struggling to find a way to complete the task and I think there should be a way to combine it into either one step, or certainly fewer steps!
I have a source data set which shows airline operations (flights) by day of week and an effective date range.

I want to transform the data so that I have a list of the data by day time series. So, translating the day of operation and effective date range to show all flights operated on a daily basis. E.g. taking the first row, it only operates on day 5 (Friday) so return a row for each flight operated within the effective date range (e.g. from 16 April 2024, the first operation is 19 April, then 26 April and so on.

Data set is thousands of rows so a short cut would save a lot of manual work.
(lighter colours is just to show the additional rows that would be generated from the single original, source data row.)

Many thanks as always for the expertise that is on this forum. It is really appreciated!

Hi,
Thanks for the replies.

The sample is just for illustration (but is the format / layout of the source data) and the output rows only represent the first few values returned (both would carry on until their last occurence before or on the 'effective to' date).
I've added an overall effective date range that covers the range of the source data and separated the source and desired output onto two sheets.

Any help greatly appreciated!

Date challenge - transform to time series2.xlsx

Hi there,

JohnTopley, this is great thank you. Have to say I'm a novice when it comes to VB work so this is a nice learning point for me! Some of the output isn't in the correct format although I'm guessing that is just due to the formatting of the source data? e.g. Arrival time of 09:10 comes out as 45:74 (based on the numerical value of 9 Oct?) and elapsed time of 02-05 comes out as date format (5/2/2024).

I'll mess around with it and see if I can get it to work cleanly.

Thanks again,
Mark.