Split Dates Between Contract and Reporting Periods

[*shudder*]

Hi,

I would like to find a way to automatically split dates for contract periods into reporting periods.

The contract data is pulled from a database using Power Query in Excel by using the following selection items.

Selection Items:

Project Information	Customer:	ABC Limited
				Key:
	Review Period Start Date:	01/04/2022		01/04/2022	31/03/2023	Year 1
	Frequency (years):	2		01/04/2023	31/03/2024	Year 2
	End Date	31/03/2024		01/04/2024	31/03/2025	N/A
	Review Number:			01/04/2025	31/03/2026	N/A
				01/04/2026	31/03/2027	N/A

Once the above is completed it populates the table below:

Source Data: Contract Costs

Key	Start	End	Labour	Materials	Hire	Expenses	Total
Year 1	30/06/2021	29/06/2022	50,624.53	14,695.74	6,812.20	853.45	72,985.93
Year 1	01/06/2022	29/06/2022	3,784.11	996.48	281.67	49.26	5,111.52
Year 1	30/06/2022	29/06/2023	56,548.52	14,891.12	4,209.19	736.08	76,384.92
Year 2	01/05/2023	29/06/2023	4,134.36	1,103.96	311.01	54.31	5,603.64
Year 2	30/06/2023	29/06/2024	61,920.62	16,534.11	4,658.05	813.45	83,926.23
N/A			0.00	0.00	0.00	0.00	0.00
N/A			0.00	0.00	0.00	0.00	0.00
N/A			0.00	0.00	0.00	0.00	0.00
N/A			0.00	0.00	0.00	0.00	0.00
		Total:	126,387.61	33,525.67	9,459.93	1,653.11	171,026.31

I want to be able to automatically split the dates from the contract periods into the reporting periods as per the start and end dates i.e the first contact period is 30.06.2021 - 29.06.2022, but the reporting period begins 01.04.2022 so the reporting period would be 01.04.2022 - 29.06.2022 as row 2. Where a contract straddles a reporting period I need to split those costs i.e. contract runs from 30.06.2022 - 29.06.2023, this needs to be split to 30.06.2022 - 31.03.2023 and 01.04.2023 - 29.06.2023 as per rows 4 & 5 below.

Expected result: Contract costs per report period

Key	Start	End	Labour	Materials	Hire	Expenses	Total
Year 1	01/04/2022	29/06/2022	12,482.76	3,623.61	1,679.72	210.44	17,996.53
Year 1	01/06/2022	29/06/2022	3,784.11	996.48	281.67	49.26	5,111.52
Year 1	30/06/2022	31/03/2023	42,605.05	11,219.34	3,171.31	554.58	57,550.28
Year 2	01/04/2023	29/06/2023	13,943.47	3,671.78	1,037.88	181.50	18,834.64
Year 2	30/06/2023	31/03/2024	46,694.24	12,468.35	3,512.63	613.42	63,288.63
N/A
N/A
N/A
N/A
		Total:	119,509.63	31,979.55	9,683.22	1,609.20	162,781.60

I have already worked out the formulas to split the costs, it is just splitting the dates I am having trouble with.

Any guidance would be much appreciated.

I am using Excel 2016.