Split a start and end date into set durations

[th081]

Hi,

I have a table in with a start date and time eg 01/12/20 04:05 and a end date and time 03/12/20 12:07 . These are in separate columns.

I would like to split the duration into 4 hour chunks starting at 01/12/20 00:00 to 04:00 then 04:00 to 08:00 etc. So for the date time in question it would have zero value for 00:00 to 04:00, 235 mins for 04:00 to 08:00 etc. I will then apply a charge per minute to the value shown, the charge per minute is different in each chunk hence the need to split.

Can anyone advise if Power Query can do this or if I would be better using VBA.

 

[sandy666]

using XL2BB post representative example and expected result

 

[th081]

The data table:

Start Date and Time	End Date and Time
02/11/2020 04:05	02/11/2020 16:07
02/11/2020 00:25	02/11/2020 20:45
01/11/2020 01:30	01/11/2020 02:45

reference table to decide on start/end date and duration of slice

Start Date and Time:	01/11/2020 00:00​
Slices (hours)	4 hours
End Date and Time	03/11/2020 04:00​

Results - from the third column it represents 4 hours as per the second table.

		Mins in slice	Mins in slice	Mins in slice	Mins in slice	Mins in slice	Mins in slice	Mins in slice	Mins in slice	Mins in slice	Mins in slice	Mins in slice	Mins in slice	Mins in slice	Mins in slice
Start Date and Time	End Date and Time	01/11/2020 00:00​	01/11/2020 04:00​	01/11/2020 08:00​	01/11/2020 12:00​	01/11/2020 16:00​	01/11/2020 20:00​	02/11/2020 00:00​	02/11/2020 04:00​	02/11/2020 08:00​	02/11/2020 12:00​	02/11/2020 16:00​	02/11/2020 20:00​	03/11/2020 00:00​	03/11/2020 04:00​
02/11/2020 04:05	02/11/2020 16:07	0	235	240	240	7	0	0	0	0	0	0	0	0	0
02/11/2020 00:25	02/11/2020 20:45	215	240	240	240	240	240	240	240	240	240	240	45	0	0
01/11/2020 01:30	01/11/2020 02:45	75	0	0	0	0	0	0	0	0	0	0	0	0	0

 

[sandy666]

did you check the link to XL2BB ?

 

[th081]

Hi Sandy666,

yes i did but this is a work machine and i dont think it will allow me to download addins etc

 

[sandy666]

sure

 

[th081]

Book2.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
4	Start Date and Time	End Date and Time				01/11/2020 00:00
5	02/11/2020 04:05	02/11/2020 16:07			Slices (hours)	4 hours
6	02/11/2020 00:25	02/11/2020 20:45			End Date and Time	03/11/2020 04:00
7	01/11/2020 01:30	01/11/2020 02:45
8
9
10	Results
11			Mins in slice	Mins in slice	Mins in slice	Mins in slice	Mins in slice	Mins in slice	Mins in slice	Mins in slice	Mins in slice	Mins in slice	Mins in slice	Mins in slice	Mins in slice	Mins in slice
12	Start Date and Time	End Date and Time	01/11/2020 00:00	01/11/2020 04:00	01/11/2020 08:00	01/11/2020 12:00	01/11/2020 16:00	01/11/2020 20:00	02/11/2020 00:00	02/11/2020 04:00	02/11/2020 08:00	02/11/2020 12:00	02/11/2020 16:00	02/11/2020 20:00	03/11/2020 00:00	03/11/2020 04:00
13	02/11/2020 04:05	02/11/2020 16:07	0	235	240	240	7	0	0	0	0	0	0	0	0	0
14	02/11/2020 00:25	02/11/2020 20:45	215	240	240	240	240	240	240	240	240	240	240	45	0	0
15	01/11/2020 01:30	01/11/2020 02:45	75	0	0	0	0	0	0	0	0	0	0	0	0	0
Sheet3

Cell Formulas
Range	Formula
F12:R12	F12	=(4/24)+E12

 

[th081]

i managed to add it

 

[sandy666]

imho, vba would be better for you (or even formula) and if you decide for vba then ask moderator to move this thread to the proper forum

 

[th081]

thank you, i will be able to do the VBA myself however i wanted to see if it could be done by Power Query as i have only just started using it and have very limited knowledge in the area.