Start end of of Sequence only.

G

Guu

New Member
Joined
Oct 4, 2024
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hello,

So I wanted to filter out the start and end time from a list of dates and values. I was able to create a new column to just show a date when the value is >0. =IF(B1>0,A1,"").

But i would like to be able to create another table of just start and end time from the list from D. skipping anything in the middle and stopping when it hits a blank cell.


1728063968718.png
like so. (manual input)

I tried using =LET(f,FILTER(A2:200,B2:B200>0),INDEX(f,SEQUENCE(ROWS(f)/2,,,2)+{0,1})) but it only give me increments of 15 minutes.

1728079744082.png
like this which i don't want.

Any ideas?


1728063988944.png
 

Attachments

  • 1728063667509.png
    1728063667509.png
    70.5 KB · Views: 3
  • 1728063883521.png
    1728063883521.png
    18.4 KB · Views: 3

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
Hello, I am not sure whether the following is what you are looking for and whether it will work but it is a trial and error:

Excel Formula: 
=LET(
times,A1:A31,
a,B1:B31,
b,SCAN(0,a=0,LAMBDA(x,y,x+y)),
c,IF(a=0,"",b),
DROP(REDUCE("",UNIQUE(FILTER(c,ISNUMBER(c))),LAMBDA(x,y,VSTACK(x,HSTACK(MIN(FILTER(times,c=y)),MAX(FILTER(times,c=y)))))),1))
 
  • Like
Reactions: Guu
Upvote 1
Solution
Thank you for the reply. I will give it a try and adjust.
 
Upvote 0
Guu said:
Thank you for the reply. I will give it a try and adjust.
Click to expand...
hagia_sofia said:
Hello, I am not sure whether the following is what you are looking for and whether it will work but it is a trial and error:

Excel Formula: 
=LET(
times,A1:A31,
a,B1:B31,
b,SCAN(0,a=0,LAMBDA(x,y,x+y)),
c,IF(a=0,"",b),
DROP(REDUCE("",UNIQUE(FILTER(c,ISNUMBER(c))),LAMBDA(x,y,VSTACK(x,HSTACK(MIN(FILTER(times,c=y)),MAX(FILTER(times,c=y)))))),1))
Click to expand...
That.. worked.. amazingly... i have so much to learn.

I know it is a lot to ask, but would you mind schooling me on how you came about creating this formula? I have been wracking my mind for 3 hours before asking my question on this forum on how to create a formula.

Please and thank you!
 
Upvote 0
Welcome to the MrExcel board!

Does this considerably shorter (& perhaps easier to understand?) one also do what you want?

24 10 05.xlsm
ABCDE
1TimeValueStartEnd
23/10/2024 00:4503/10/2024 07:033/10/2024 11:01
33/10/2024 05:2903/10/2024 20:504/10/2024 02:41
43/10/2024 07:0334/10/2024 04:004/10/2024 04:00
53/10/2024 09:3624/10/2024 06:274/10/2024 06:27
63/10/2024 09:4564/10/2024 19:004/10/2024 21:57
73/10/2024 10:305
83/10/2024 11:013
93/10/2024 11:030
103/10/2024 13:480
113/10/2024 20:390
123/10/2024 20:503
133/10/2024 21:035
143/10/2024 23:324
154/10/2024 02:413
164/10/2024 03:100
174/10/2024 04:006
184/10/2024 04:080
194/10/2024 06:140
204/10/2024 06:273
214/10/2024 08:070
224/10/2024 09:220
234/10/2024 12:440
244/10/2024 13:330
254/10/2024 14:180
264/10/2024 15:410
274/10/2024 16:390
284/10/2024 18:220
294/10/2024 19:0084
304/10/2024 21:0655
314/10/2024 21:5710
324/10/2024 23:330
33
Start End
Cell Formulas
RangeFormula
D2:E6D2=HSTACK(FILTER(A2:A32,(B2:B32>0)*(B1:B31=0)),FILTER(A2:A32,(B2:B32>0)*(B3:B33=0)))
Dynamic array formulas.



.. or maybe just as two separate filter formulas?

24 10 05.xlsm
ABCDE
1TimeValueStartEnd
23/10/2024 00:4503/10/2024 07:033/10/2024 11:01
33/10/2024 05:2903/10/2024 20:504/10/2024 02:41
43/10/2024 07:0334/10/2024 04:004/10/2024 04:00
53/10/2024 09:3624/10/2024 06:274/10/2024 06:27
63/10/2024 09:4564/10/2024 19:004/10/2024 21:57
73/10/2024 10:305
83/10/2024 11:013
93/10/2024 11:030
103/10/2024 13:480
113/10/2024 20:390
123/10/2024 20:503
133/10/2024 21:035
143/10/2024 23:324
154/10/2024 02:413
164/10/2024 03:100
174/10/2024 04:006
184/10/2024 04:080
194/10/2024 06:140
204/10/2024 06:273
214/10/2024 08:070
224/10/2024 09:220
234/10/2024 12:440
244/10/2024 13:330
254/10/2024 14:180
264/10/2024 15:410
274/10/2024 16:390
284/10/2024 18:220
294/10/2024 19:0084
304/10/2024 21:0655
314/10/2024 21:5710
324/10/2024 23:330
33
Start End (2)
Cell Formulas
RangeFormula
D2:D6D2=FILTER(A2:A32,(B2:B32>0)*(B1:B31=0))
E2:E6E2=FILTER(A2:A32,(B2:B32>0)*(B3:B33=0))
Dynamic array formulas.
 
  • Like
Reactions: Guu
Upvote 0
Peter_SSs said:
Welcome to the MrExcel board!

Does this considerably shorter (& perhaps easier to understand?) one also do what you want?

24 10 05.xlsm
ABCDE
1TimeValueStartEnd
23/10/2024 00:4503/10/2024 07:033/10/2024 11:01
33/10/2024 05:2903/10/2024 20:504/10/2024 02:41
43/10/2024 07:0334/10/2024 04:004/10/2024 04:00
53/10/2024 09:3624/10/2024 06:274/10/2024 06:27
63/10/2024 09:4564/10/2024 19:004/10/2024 21:57
73/10/2024 10:305
83/10/2024 11:013
93/10/2024 11:030
103/10/2024 13:480
113/10/2024 20:390
123/10/2024 20:503
133/10/2024 21:035
143/10/2024 23:324
154/10/2024 02:413
164/10/2024 03:100
174/10/2024 04:006
184/10/2024 04:080
194/10/2024 06:140
204/10/2024 06:273
214/10/2024 08:070
224/10/2024 09:220
234/10/2024 12:440
244/10/2024 13:330
254/10/2024 14:180
264/10/2024 15:410
274/10/2024 16:390
284/10/2024 18:220
294/10/2024 19:0084
304/10/2024 21:0655
314/10/2024 21:5710
324/10/2024 23:330
33
Start End
Cell Formulas
RangeFormula
D2:E6D2=HSTACK(FILTER(A2:A32,(B2:B32>0)*(B1:B31=0)),FILTER(A2:A32,(B2:B32>0)*(B3:B33=0)))
Dynamic array formulas.



.. or maybe just as two separate filter formulas?

24 10 05.xlsm
ABCDE
1TimeValueStartEnd
23/10/2024 00:4503/10/2024 07:033/10/2024 11:01
33/10/2024 05:2903/10/2024 20:504/10/2024 02:41
43/10/2024 07:0334/10/2024 04:004/10/2024 04:00
53/10/2024 09:3624/10/2024 06:274/10/2024 06:27
63/10/2024 09:4564/10/2024 19:004/10/2024 21:57
73/10/2024 10:305
83/10/2024 11:013
93/10/2024 11:030
103/10/2024 13:480
113/10/2024 20:390
123/10/2024 20:503
133/10/2024 21:035
143/10/2024 23:324
154/10/2024 02:413
164/10/2024 03:100
174/10/2024 04:006
184/10/2024 04:080
194/10/2024 06:140
204/10/2024 06:273
214/10/2024 08:070
224/10/2024 09:220
234/10/2024 12:440
244/10/2024 13:330
254/10/2024 14:180
264/10/2024 15:410
274/10/2024 16:390
284/10/2024 18:220
294/10/2024 19:0084
304/10/2024 21:0655
314/10/2024 21:5710
324/10/2024 23:330
33
Start End (2)
Cell Formulas
RangeFormula
D2:D6D2=FILTER(A2:A32,(B2:B32>0)*(B1:B31=0))
E2:E6E2=FILTER(A2:A32,(B2:B32>0)*(B3:B33=0))
Dynamic array formulas.
Click to expand...

FYI to anyone reading this, this also works for my dilemma with minor tweaking! thank you so much!
 
Upvote 0
You are welcome. Thanks for the follow-up. :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Lambda and custom sequence with interference of string
Replies
3
Views
95
StephenCrump
S
R
  • Solved
How to independently sort multiple columns of this SEQUENCE output without effecting the positions of the columns and of the blanks?
2
Replies
15
Views
431
Rnkhch
R
K
  • Solved
Modify formula to insert blank rows into sequence
Replies
3
Views
213
Fluff
Fluff
R
  • Question Question
Trying to find start and end times in a date/time column with a difficult twist.
Replies
8
Views
392
felixstraube
F
E
  • Solved
Is there a way to ignore year in calculating duration of days?
Replies
3
Views
118
Joe4
Joe4

Forum statistics

Threads
1,222,830
Messages
6,168,509
Members
452,194
Latest member
Lowie27

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top