Creating a Formula to TextJoin

[Shazir]

Hi, I hope you all are doing well.

I was looking for a formula that will solve the below picture Example.

I want to get the Cells in "Late" Column if C2,D2,E2 have such format "Late - 11:00 AM" then "Late" should be converted with Date as available in C1,D1,E1 and time should be as it is.

and result would be as = 8/1/2020 - 11:00 AM

 

[Shazir]

Thank you so much for your help.
Sir, Peter_SSs
=ARRAYFORMULA(TEXTJOIN(", ",TRUE,IF(LEFT(C2:AG2,4)="Late",TEXT(C$1:AG$1+RIGHT(C2:AG2,8),"mm/dd/yyyy - (hh:mm AM/PM)"),"")) )

Above formula is working perfectly for single status that is “Late”, “Early” ……etc.

But there will be on or more status in cell “C2” then formula will show wrong values.Requesting you to please review this formula and make further changes to make it perfect I want result attached in below picture from Column “C” as available in Column “AI:AN” looking for your respected response.

 

[jasonb75]

As I pointed out earlier, Peter's suggestion would have been based on the information that you provided in your original post.
It is wise to make sure that the information is complete and that you are not going to make any significant changes to it before asking for formulas that refer to it.

Try array confirming this in AI2, then drag right and fill down.

=TEXTJOIN(", ",TRUE,IFERROR(TEXT($C$1:$AG$1+MID($C2:$AG2,SEARCH($C2:$AG2,AI$1)+LEN(AI$1)+3,8),"mm/dd/yyyy - (hh:mm AM/PM)"),""))

 

[Shazir]

I understand that.

but even after confirming the array its result is empty.

 

[jasonb75]

I can see from that screen capture that you have not confirmed the array.

 

[Shazir]

I did.

 

[jasonb75]

Oops, I had to check it 4 times to find my mistake, try this version instead.

=TEXTJOIN(", ",TRUE,IFERROR(TEXT($C$1:$AG$1+MID($C2:$AG2,SEARCH(AI$1,$C2:$AG2)+LEN(AI$1)+3,8),"mm/dd/yyyy - (hh:mm AM/PM)"),""))

 

[Shazir]

[B]jasonb75[/B] Thank you so much for making this unbelievable formula you are literally a PRO Thanks.

But it should not mention the time with "Absent" and "Extra Shift"

As you can see in attached picture

 

[jasonb75]

See if this works, array confirm in AK2 then drag and fill columns AK and AL.

=TEXTJOIN(", ",TRUE,IFERROR(TEXT($C$1:$AG$1+MID($C2:$AG2,SEARCH(AK$1,$C2:$AG2)+LEN(AK$1)+3,8),"mm/dd/yyyy"),""))

 

[Shazir]

jasonb75

I just wanted to say a BIG THANK YOU to you for putting in so much effort. You deserve 5-Stars. Thank you.

 

[jasonb75]

You're welcome

Maybe just 4 stars though, @Peter_SSs had to correct the mistake in my first reply