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

 

[jasonb75]

Excel 2016 does not have the textjoin function or any equivalent.

You can get the date and time for an individual day by using =IF(LEFT(C2="Late",C$1+RIGHT(C2,8),"") and applying the desired format to the cell.

 

[Shazir]

jasonb75

I have updated my excel to 365. Please consider it again.

 

[Shazir]

I was looking for such formula.

=TEXTJOIN(" - ",TRUE,IF(LEFT(C2:T2="Late",C$1:T1+RIGHT(C2:T2,8),"")))

But its not working

 

[jasonb75]

In that case, please update your profile so that it shows the correct version.

Try this, which might need to be array confirmed with Ctrl Shift Enter.

=TEXTJOIN(" - ",TRUE,IF(LEFT(C2:T2="Late",TEXT(C$1:T$1+RIGHT(C2:T2,8),"m/d/yyyy - AM/PM","")))

 

[Shazir]

Its not working this error gets appear

Wrong number of arguments to IF. Expected between 2 and 3 arguments, but got 1 arguments.

 

[Peter_SSs]

Try this instead

=TEXTJOIN(", ",TRUE,IF(LEFT(C3:T3,4)="Late",TEXT(C$1:T$1+RIGHT(C3:T3,8),"m/d/yyyy - hh:mm AM/PM"),""))

 

[jasonb75]

Oops, I just added the text part, didn't check the other parts for errors.

 

[Peter_SSs]

Having just upgraded to 365 you may not yet have the FILTER function, but if you do, another option would be

=TEXTJOIN(", ",1,FILTER(TEXT(C$1:T$1+RIGHT(C3:T3,8),"m/d/yyyy - hh:mm AM/PM"),LEFT(C3:T3,4)="Late",""))

 

[Shazir]

Thank you so much to both of you. Its working perfectly.

Can you please help me with further that i am using this formula.

=TRIM(IF(Sheet1!C3>TIMEVALUE("08:30 AM"),"Late"&" - "&TEXT(Sheet1!C3,"HH:MM AM/PM"),"")&" ; "& IF(Sheet1!D3<>TIMEVALUE("00:00:00"),"Early"&" - "&TEXT(Sheet1!D3,"HH:MM AM/PM"),"")&" ; "& IF(Sheet1!E3<>"","Extra Shift","")&" ; "& IF(Sheet2!C3="A","Absent","")&" ; "& IF(Sheet2!D3<>TIMEVALUE("00:00:00"),"Short"&" - "&TEXT(Sheet2!E3,"HH:MM AM/PM"),""))&" ; "& IF(Sheet2!E3<>TIMEVALUE("00:00:00"),"Half"&" - "&TEXT(Sheet2!E3,"HH:MM AM/PM"),"")

and it gives me belowresult

Late - 09:45 AM ; ; Extra Shift ; ; ;

i want to remove the extra ;

and

I have found this Substitute solution in this forum with below formula but could not modify it.

Formula Decreasin Request

Using below formula to get desired result from attendance sheet i just added this &" // "& in formula so that i could separate them while making report from (Text to Column) and now Exceed 8192 char in formula I would request in this regard please decrease this or make it correct...

www.mrexcel.com

=Data!C$1&" => "&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(
IF(ISNUMBER(LEFT(Data!C3)*1),"£",Data!C3)&IF(OR(Data!C3:D3<>""),TEXT(Data!A3,"MM/DD/YY-DDDD-"),"")&Data!E3&" "&
IF(ISNUMBER(LEFT(Data!C4)*1),"£",Data!C4)&IF(OR(Data!C4:D4<>""),TEXT(Data!A4,"MM/DD/YY-DDDD-"),"")&Data!E4&" "
)," "," //"),"L","*Leave*-"),"A","*Absent*-"),"£","*Late*-"),"-"," - ")