CODEWITHSUNDEEP
excelexcel-formulaformula
mattf
mattf

Reputation: 13

How to split excel records into multiple records using formula

I have data in the following format:

Start date End date Start time End time Volume Price
9/12/22 9/12/22 10:00 12:00 50 300

Is there a way using formulas to split that record into hourly data? I was hoping for something that could work automatically without using vba if possible.

This is the output I am looking for:

Start Date End Date Time Volume Price
9/12/22 9/12/22 10 50 300
9/12/22 9/12/22 11 50 300

This could be done by creating an empty date time series and using sum-ifs. I would like to avoid that if possible as each day only has a few hours of prices/volumes

Upvotes: 0

Views: 101

Answers (1)

JvdV
JvdV

Reputation: 75860

For ms365, try:

enter image description here

Formula in A5:

=LET(r,A2:F3,REDUCE(HSTACK(A1:B1,"Time",E1:F1),SEQUENCE(ROWS(r)),LAMBDA(a,b,LET(x,INDEX(r,b,1)+INDEX(r,b,3),y,INDEX(r,b,2)+INDEX(r,b,4),VSTACK(a,DROP(REDUCE(0,SEQUENCE(--TEXT(y-x,"[hh]")),LAMBDA(c,d,VSTACK(c,HSTACK(INT(x+TIME(d-{1,0},,)),INDEX(r,b,3)+TIME(d-1,,),INDEX(r,b,{5,6}))))),1))))))

Above is flexible enough to have Start- and End Date to be different.

Upvotes: 1

Related Questions