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]

Power Query documentation - Power Query

Power Query is used to filter, combine, and mash up data from one or more supported data sources.

docs.microsoft.com

i wanted to see if it could be done by Power Query

too much work
you can try translate vba to M, everything you need is in the documentation

 

[sandy666]

just for fun, on cito I did something like this

Duration	0	1	2	3	4	5
240 x 0	01/11/2020
240 x 3	02/11/2020	02/11/2020	02/11/2020	02/11/2020
240 x 5	02/11/2020	02/11/2020	02/11/2020	02/11/2020	02/11/2020	02/11/2020

 

[sandy666]

or like this with different layout than yours

Days	Hours	Minutes	0	1	2	3	4	5
0	7	30	01/11/2020
3	0	12	02/11/2020	02/11/2020	02/11/2020	02/11/2020
5	2	0	02/11/2020	02/11/2020	02/11/2020	02/11/2020	02/11/2020	02/11/2020

 

[sandy666]

or even like this

Duration	Days	Hours	Minutes	0	1	2	3	4	5
0	0	1	15	01/11/2020
3	0	12	2	02/11/2020	02/11/2020	02/11/2020	02/11/2020
5	0	20	20	02/11/2020	02/11/2020	02/11/2020	02/11/2020	02/11/2020	02/11/2020

 

[shaowu459]

please check the expected results in post #7, i'm not quite clear about the logic. if you can make more clarification i think it could be done using PQ.

 

[shaowu459]

In my understanding, the expected result is:

PQ小题.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Start Date and Time	End Date and Time				2020/11/1 00:00:00
2	2020/11/2 04:05:00	2020/11/2 16:07:00			Slices (hours)	4 hours
3	2020/11/2 00:25:00	2020/11/2 20:45:00			End Date and Time	2020/11/3 04:00:00
4	2020/11/1 01:30:00	2020/11/1 02:45:00
5
6
7	Results
8
9
10	Start Date and Time	End Date and Time	2020/11/1 0:00:00	2020/11/1 4:00:00	2020/11/1 8:00:00	2020/11/1 12:00:00	2020/11/1 16:00:00	2020/11/1 20:00:00	2020/11/2 0:00:00	2020/11/2 4:00:00	2020/11/2 8:00:00	2020/11/2 12:00:00	2020/11/2 16:00:00	2020/11/2 20:00:00	2020/11/3 0:00:00
11	2020/11/2 04:05:00	2020/11/2 16:07:00	0	0	0	0	0	0	0	235	240	240	7	0	0
12	2020/11/2 00:25:00	2020/11/2 20:45:00	0	0	0	0	0	0	215	240	240	240	240	45	0
13	2020/11/1 01:30:00	2020/11/1 02:45:00	75	0	0	0	0	0	0	0	0	0	0	0	0
Sheet5

let
    Source = Excel.CurrentWorkbook(){[Name="Table10"]}[Content],
    start = DateTime.From("2020/11/1 0:00:00"),
    end = DateTime.From("2020/11/3 4:00:00"),
    lst = List.DateTimes(start,Number.From(end-start)*6, #duration(0,4,0,0)),
    res = List.Accumulate(
                            lst,
                            Source,
                            (x,y)=>
                                    Table.AddColumn(
                                                    x,
                                                    Text.From(y),
                                                    (z)=>
                                                          let
                                                              s=z[Start Date and Time],
                                                              e=z[End Date and Time],
                                                              d=#duration(0,4,0,0),
                                                              a=if y+d<=s then 0
                                                                else
                                                                    (if y+d>=e then e else y+d)-(if s<=y then y else s),
                                                              b=Number.From(a)*1440
                                                          in
                                                              if b<0 then 0 else b
                                                    )
    )
in
    res