Find first and last row that includes specific text

[Ahlis1979]

In the column named Example it should show the time different between every status change and when the when status is CLOSE I want to do a time different between status NEW and status CLOSE.

Hope you guys have some great answer to this question.

Best Regards
Ahlis

 

[Fluff]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)


Also can you please post some sample data.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Ahlis1979]

Hi,

Thanks for the info. I have updated my profile now.

I'm using Excel 2016

Will check XL2BB Add-In.

Best Regards
Ahlis

 

[Ahlis1979]

Here is from the sheet where the data should be added: The code for both leadtime (Ledtid) and time between NEW and CLOSE should be in column J.
Column C & I is the column that has the data for criteria and column G is where it should do time is in.

MaximoData2020.xlsx
	C	D	E	F	G	H	I	J
1	Status	Ändrat av	AO Num	kontroll	Korrekt statusdatum	Status	Korrekt AO	Ledtid
2	NEW	MAXADMIN	175818	Träff	2020-01-01 01:02	FU	175818	0:00:00
3	WSCH	MAXADMIN	175818	Träff	2020-01-01 01:02	FU	175818	224:31:00
4	WAIT FOR SCHED	KN5711	175818	Träff	2020-01-10 09:33	FU	175818	244:02:00
5	SCHEDULED	KN5711	175818	Träff	2020-01-20 13:35	FU	175818	96:39:00
6	ASSIGNED	KN5711	175818	Träff	2020-01-24 14:14	FU	175818	69:28:00
7	INPRG	DS5073	175818	Träff	2020-01-27 11:42	FU	175818	19:05:00
8	CLOSE	DS5073	175818	Träff	2020-01-28 06:47	FU	175818	653:45:00
9	NEW	MAXADMIN	175829	Träff	2020-01-02 01:01	AUUP	175829	0:00:00
10	WSCH	MAXADMIN	175829	Träff	2020-01-02 01:01	AUUP	175829	125:11:00
11	PLANNED	DS69207	175829	Träff	2020-01-07 06:12	AUUP	175829	175:06:00
12	WAIT FOR SCHED	DS69207	175829	Träff	2020-01-14 13:18	AUUP	175829	64:45:00
13	ASSIGNED	DS69207	175829	Träff	2020-01-17 06:03	AUUP	175829	175:26:00
14	COMP	FW5674	175829	Träff	2020-01-24 13:29	AUUP	175829	145:46:00
15	CLOSE	FW5674	175829	Träff	2020-01-30 15:15	AUUP	175829	686:14:00
16	NEW	MAXADMIN	175844	Träff	2020-01-02 01:02	AUUP	175844	0:00:00
17	WSCH	MAXADMIN	175844	Träff	2020-01-02 01:02	AUUP	175844	125:09:00
Wostatus

Cell Formulas
Range	Formula
E2:E17	E2	=VALUE(A2)
F2:F17	F2	=IFERROR(IF(MATCH(I2,Arbetsordrar!$B$2:$B$7275,0)>0,"Träff","Miss"),"MISS")
G2:G17	G2	=TEXT($B2-1462,"ÅÅÅÅ-MM-DD tt:mm")
H2:H17	H2	=IFERROR(INDEX(Arbetsordrar!$Z$2:$Z$7275,MATCH(Wostatus!I2,Arbetsordrar!$B$2:$B$7275,0),1),"Data saknas!")
I2:I17	I2	=IFERROR(IF(A2="","",VALUE(A2)),"")
J2:J7	J2	=IF(C2="CLOSE",F&INDEX(ROW(),MATCH("CLOSE",C8,0),1)-G2,IF(I3=I2,G3-G2,""))
J8:J17	J8	=IF(C8="CLOSE",INDIRECT(ADDRESS(ROW(),7),TRUE)-INDIRECT(ADDRESS(MIN(IF(ISNUMBER(SEARCH("NEW",C2:C13)),ROW(C2:C13))),7)),IF(I9=I8,G9-G8,""))

Nice Add-In.

Best Regards
Ahlis

 

[Fluff]

Thanks for that, how about

+Fluff 1.xlsm
	C	D	E	F	G	H	I	J
1	Status	Ändrat av	AO Num	kontroll	Korrekt statusdatum	Status	Korrekt AO	Ledtid
2	NEW	MAXADMIN	175818	Träff	01/01/2020 01:02	FU	175818	0:00:00
3	WSCH	MAXADMIN	175818	Träff	01/01/2020 01:02	FU	175818	224:31:00
4	WAIT FOR SCHED	KN5711	175818	Träff	10/01/2020 09:33	FU	175818	244:02:00
5	SCHEDULED	KN5711	175818	Träff	20/01/2020 13:35	FU	175818	96:39:00
6	ASSIGNED	KN5711	175818	Träff	24/01/2020 14:14	FU	175818	69:28:00
7	INPRG	DS5073	175818	Träff	27/01/2020 11:42	FU	175818	19:05:00
8	CLOSE	DS5073	175818	Träff	28/01/2020 06:47	FU	175818	653:45:00
9	NEW	MAXADMIN	175829	Träff	02/01/2020 01:01	AUUP	175829	0:00:00
10	WSCH	MAXADMIN	175829	Träff	02/01/2020 01:01	AUUP	175829	125:11:00
11	PLANNED	DS69207	175829	Träff	07/01/2020 06:12	AUUP	175829	175:06:00
12	WAIT FOR SCHED	DS69207	175829	Träff	14/01/2020 13:18	AUUP	175829	64:45:00
13	ASSIGNED	DS69207	175829	Träff	17/01/2020 06:03	AUUP	175829	175:26:00
14	COMP	FW5674	175829	Träff	24/01/2020 13:29	AUUP	175829	145:46:00
15	CLOSE	FW5674	175829	Träff	30/01/2020 15:15	AUUP	175829	686:14:00
16	NEW	MAXADMIN	175844	Träff	02/01/2020 01:02	AUUP	175844	0:00:00
List

Cell Formulas
Range	Formula
J2:J16	J2	=IF(C2="New",0,IF(C2="Close",G2-LOOKUP("new",C$2:C2,G$2:G2),G3-G2))

 

[Ahlis1979]

Thanks!

Worked well!

Best Regards
Ahlis

 

[Fluff]

You're welcome & thanks for the feedback.