Formula Decreasin Request

[HSAR]

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.




=Data!C$1&" => "&IF(Data!C3="","",IF(ISNUMBER(LEFT(Data!C3)*1),"*Late* - ",IF(Data!C3="L","*Leave* - ",IF(Data!C3="A","*Absent* - "))))&IF(OR(Data!C3<>"",Data!D3<>""),TEXT(Data!$A$3,"MM/DD/YY - "),"")&IF(OR(Data!C3<>"",Data!D3<>""),TEXT(Data!$B$3,"DDDD - "),"")&IF(Data!E3<>"",Data!E3,"")&" // "&
IF(Data!C4="","",IF(ISNUMBER(LEFT(Data!C4)*1),"*Late* - ",IF(Data!C4="L","*Leave* - ",IF(Data!C4="A","*Absent* - "))))&IF(OR(Data!C4<>"",Data!D4<>""),TEXT(Data!$A$4,"MM/DD/YY - "),"")&IF(OR(Data!C4<>"",Data!D4<>""),TEXT(Data!$B$4,"DDDD - "),"")&IF(Data!E4<>"",Data!E4,"")&" // "&
IF(Data!C5="","",IF(ISNUMBER(LEFT(Data!C5)*1),"*Late* - ",IF(Data!C5="L","*Leave* - ",IF(Data!C5="A","*Absent* - "))))&IF(OR(Data!C5<>"",Data!D5<>""),TEXT(Data!$A$5,"MM/DD/YY - "),"")&IF(OR(Data!C5<>"",Data!D5<>""),TEXT(Data!$B$5,"DDDD - "),"")&IF(Data!E5<>"",Data!E5,"")&" // "&
IF(Data!C6="","",IF(ISNUMBER(LEFT(Data!C6)*1),"*Late* - ",IF(Data!C6="L","*Leave* - ",IF(Data!C6="A","*Absent* - "))))&IF(OR(Data!C6<>"",Data!D6<>""),TEXT(Data!$A$6,"MM/DD/YY - "),"")&IF(OR(Data!C6<>"",Data!D6<>""),TEXT(Data!$B$6,"DDDD - "),"")&IF(Data!E6<>"",Data!E6,"")&" // "&
IF(Data!C7="","",IF(ISNUMBER(LEFT(Data!C7)*1),"*Late* - ",IF(Data!C7="L","*Leave* - ",IF(Data!C7="A","*Absent* - "))))&IF(OR(Data!C7<>"",Data!D7<>""),TEXT(Data!$A$7,"MM/DD/YY - "),"")&IF(OR(Data!C7<>"",Data!D7<>""),TEXT(Data!$B$7,"DDDD - "),"")&IF(Data!E7<>"",Data!E7,"")&" // "&
IF(Data!C8="","",IF(ISNUMBER(LEFT(Data!C8)*1),"*Late* - ",IF(Data!C8="L","*Leave* - ",IF(Data!C8="A","*Absent* - "))))&IF(OR(Data!C8<>"",Data!D8<>""),TEXT(Data!$A$8,"MM/DD/YY - "),"")&IF(OR(Data!C8<>"",Data!D8<>""),TEXT(Data!$B$8,"DDDD - "),"")&IF(Data!E8<>"",Data!E8,"")&" // "&
IF(Data!C9="","",IF(ISNUMBER(LEFT(Data!C9)*1),"*Late* - ",IF(Data!C9="L","*Leave* - ",IF(Data!C9="A","*Absent* - "))))&IF(OR(Data!C9<>"",Data!D9<>""),TEXT(Data!$A$9,"MM/DD/YY - "),"")&IF(OR(Data!C9<>"",Data!D9<>""),TEXT(Data!$B$9,"DDDD - "),"")&IF(Data!E9<>"",Data!E9,"")&" // "&
IF(Data!C10="","",IF(ISNUMBER(LEFT(Data!C10)*1),"*Late* - ",IF(Data!C10="L","*Leave* - ",IF(Data!C10="A","*Absent* - "))))&IF(OR(Data!C10<>"",Data!D10<>""),TEXT(Data!$A$10,"MM/DD/YY - "),"")&IF(OR(Data!C10<>"",Data!D10<>""),TEXT(Data!$B$10,"DDDD - "),"")&IF(Data!E10<>"",Data!E10,"")&" // "&
IF(Data!C11="","",IF(ISNUMBER(LEFT(Data!C11)*1),"*Late* - ",IF(Data!C11="L","*Leave* - ",IF(Data!C11="A","*Absent* - "))))&IF(OR(Data!C11<>"",Data!D11<>""),TEXT(Data!$A$11,"MM/DD/YY - "),"")&IF(OR(Data!C11<>"",Data!D11<>""),TEXT(Data!$B$11,"DDDD - "),"")&IF(Data!E11<>"",Data!E11,"")&"// "&
IF(Data!C12="","",IF(ISNUMBER(LEFT(Data!C12)*1),"*Late* - ",IF(Data!C12="L","*Leave* - ",IF(Data!C12="A","*Absent* - "))))&IF(OR(Data!C12<>"",Data!D12<>""),TEXT(Data!$A$12,"MM/DD/YY - "),"")&IF(OR(Data!C12<>"",Data!D12<>""),TEXT(Data!$B$12,"DDDD - "),"")&IF(Data!E12<>"",Data!E12,"")&" // "&
IF(Data!C13="","",IF(ISNUMBER(LEFT(Data!C13)*1),"*Late* - ",IF(Data!C13="L","*Leave* - ",IF(Data!C13="A","*Absent* - "))))&IF(OR(Data!C13<>"",Data!D13<>""),TEXT(Data!$A$13,"MM/DD/YY - "),"")&IF(OR(Data!C13<>"",Data!D13<>""),TEXT(Data!$B$13,"DDDD - "),"")&IF(Data!E13<>"",Data!E13,"")&" // "&
IF(Data!C14="","",IF(ISNUMBER(LEFT(Data!C14)*1),"*Late* - ",IF(Data!C14="L","*Leave* - ",IF(Data!C14="A","*Absent* - "))))&IF(OR(Data!C14<>"",Data!D14<>""),TEXT(Data!$A$14,"MM/DD/YY - "),"")&IF(OR(Data!C14<>"",Data!D14<>""),TEXT(Data!$B$14,"DDDD - "),"")&IF(Data!E14<>"",Data!E14,"")&" // "&
IF(Data!C15="","",IF(ISNUMBER(LEFT(Data!C15)*1),"*Late* - ",IF(Data!C15="L","*Leave* - ",IF(Data!C15="A","*Absent* - "))))&IF(OR(Data!C15<>"",Data!D15<>""),TEXT(Data!$A$15,"MM/DD/YY - "),"")&IF(OR(Data!C15<>"",Data!D15<>""),TEXT(Data!$B$15,"DDDD - "),"")&IF(Data!E15<>"",Data!E15,"")&" // "&
IF(Data!C16="","",IF(ISNUMBER(LEFT(Data!C16)*1),"*Late* - ",IF(Data!C16="L","*Leave* - ",IF(Data!C16="A","*Absent* - "))))&IF(OR(Data!C16<>"",Data!D16<>""),TEXT(Data!$A$16,"MM/DD/YY - "),"")&IF(OR(Data!C16<>"",Data!D16<>""),TEXT(Data!$B$16,"DDDD - "),"")&IF(Data!E16<>"",Data!E16,"")&" // "&
IF(Data!C17="","",IF(ISNUMBER(LEFT(Data!C17)*1),"*Late* - ",IF(Data!C17="L","*Leave* - ",IF(Data!C17="A","*Absent* - "))))&IF(OR(Data!C17<>"",Data!D17<>""),TEXT(Data!$A$17,"MM/DD/YY - "),"")&IF(OR(Data!C17<>"",Data!D17<>""),TEXT(Data!$B$17,"DDDD - "),"")&IF(Data!E17<>"",Data!E17,"")&" // "&
IF(Data!C18="","",IF(ISNUMBER(LEFT(Data!C18)*1),"*Late* - ",IF(Data!C18="L","*Leave* - ",IF(Data!C18="A","*Absent* - "))))&IF(OR(Data!C18<>"",Data!D18<>""),TEXT(Data!$A$18,"MM/DD/YY - "),"")&IF(OR(Data!C18<>"",Data!D18<>""),TEXT(Data!$B$18,"DDDD - "),"")&IF(Data!E18<>"",Data!E18,"")&" // "&
IF(Data!C19="","",IF(ISNUMBER(LEFT(Data!C19)*1),"*Late* - ",IF(Data!C19="L","*Leave* - ",IF(Data!C19="A","*Absent* - "))))&IF(OR(Data!C19<>"",Data!D19<>""),TEXT(Data!$A$19,"MM/DD/YY - "),"")&IF(OR(Data!C19<>"",Data!D19<>""),TEXT(Data!$B$19,"DDDD - "),"")&IF(Data!E19<>"",Data!E19,"")&" // "&
IF(Data!C20="","",IF(ISNUMBER(LEFT(Data!C20)*1),"*Late* - ",IF(Data!C20="L","*Leave* - ",IF(Data!C20="A","*Absent* - "))))&IF(OR(Data!C20<>"",Data!D20<>""),TEXT(Data!$A$20,"MM/DD/YY - "),"")&IF(OR(Data!C20<>"",Data!D20<>""),TEXT(Data!$B$20,"DDDD - "),"")&IF(Data!E20<>"",Data!E20,"")&" // "&
IF(Data!C21="","",IF(ISNUMBER(LEFT(Data!C21)*1),"*Late* - ",IF(Data!C21="L","*Leave* - ",IF(Data!C21="A","*Absent* - "))))&IF(OR(Data!C21<>"",Data!D21<>""),TEXT(Data!$A$21,"MM/DD/YY - "),"")&IF(OR(Data!C21<>"",Data!D21<>""),TEXT(Data!$B$21,"DDDD - "),"")&IF(Data!E21<>"",Data!E21,"")&" // "&
IF(Data!C22="","",IF(ISNUMBER(LEFT(Data!C22)*1),"*Late* - ",IF(Data!C22="L","*Leave* - ",IF(Data!C22="A","*Absent* - "))))&IF(OR(Data!C22<>"",Data!D22<>""),TEXT(Data!$A$22,"MM/DD/YY - "),"")&IF(OR(Data!C22<>"",Data!D22<>""),TEXT(Data!$B$22,"DDDD - "),"")&IF(Data!E22<>"",Data!E22,"")&" // "&
IF(Data!C23="","",IF(ISNUMBER(LEFT(Data!C23)*1),"*Late* - ",IF(Data!C23="L","*Leave* - ",IF(Data!C23="A","*Absent* - "))))&IF(OR(Data!C23<>"",Data!D23<>""),TEXT(Data!$A$23,"MM/DD/YY - "),"")&IF(OR(Data!C23<>"",Data!D23<>""),TEXT(Data!$B$23,"DDDD - "),"")&IF(Data!E23<>"",Data!E23,"")&" // "&
IF(Data!C24="","",IF(ISNUMBER(LEFT(Data!C24)*1),"*Late* - ",IF(Data!C24="L","*Leave* - ",IF(Data!C24="A","*Absent* - "))))&IF(OR(Data!C24<>"",Data!D24<>""),TEXT(Data!$A$24,"MM/DD/YY - "),"")&IF(OR(Data!C24<>"",Data!D24<>""),TEXT(Data!$B$24,"DDDD - "),"")&IF(Data!E24<>"",Data!E24,"")&" // "&
IF(Data!C25="","",IF(ISNUMBER(LEFT(Data!C25)*1),"*Late* - ",IF(Data!C25="L","*Leave* - ",IF(Data!C25="A","*Absent* - "))))&IF(OR(Data!C25<>"",Data!D25<>""),TEXT(Data!$A$25,"MM/DD/YY - "),"")&IF(OR(Data!C25<>"",Data!D25<>""),TEXT(Data!$B$25,"DDDD - "),"")&IF(Data!E25<>"",Data!E25,"")&" // "&
IF(Data!C26="","",IF(ISNUMBER(LEFT(Data!C26)*1),"*Late* - ",IF(Data!C26="L","*Leave* - ",IF(Data!C26="A","*Absent* - "))))&IF(OR(Data!C26<>"",Data!D26<>""),TEXT(Data!$A$26,"MM/DD/YY - "),"")&IF(OR(Data!C26<>"",Data!D26<>""),TEXT(Data!$B$26,"DDDD - "),"")&IF(Data!E26<>"",Data!E26,"")&" // "&
IF(Data!C27="","",IF(ISNUMBER(LEFT(Data!C27)*1),"*Late* - ",IF(Data!C27="L","*Leave* - ",IF(Data!C27="A","*Absent* - "))))&IF(OR(Data!C27<>"",Data!D27<>""),TEXT(Data!$A$27,"MM/DD/YY - "),"")&IF(OR(Data!C27<>"",Data!D27<>""),TEXT(Data!$B$27,"DDDD - "),"")&IF(Data!E27<>"",Data!E27,"")&" // "&
IF(Data!C28="","",IF(ISNUMBER(LEFT(Data!C28)*1),"*Late* - ",IF(Data!C28="L","*Leave* - ",IF(Data!C28="A","*Absent* - "))))&IF(OR(Data!C28<>"",Data!D28<>""),TEXT(Data!$A$28,"MM/DD/YY - "),"")&IF(OR(Data!C28<>"",Data!D28<>""),TEXT(Data!$B$28,"DDDD - "),"")&IF(Data!E28<>"",Data!E28,"")&" // "&
IF(Data!C29="","",IF(ISNUMBER(LEFT(Data!C29)*1),"*Late* - ",IF(Data!C29="L","*Leave* - ",IF(Data!C29="A","*Absent* - "))))&IF(OR(Data!C29<>"",Data!D29<>""),TEXT(Data!$A$29,"MM/DD/YY - "),"")&IF(OR(Data!C29<>"",Data!D29<>""),TEXT(Data!$B$29,"DDDD - "),"")&IF(Data!E29<>"",Data!E29,"")&" // "&
IF(Data!C30="","",IF(ISNUMBER(LEFT(Data!C30)*1),"*Late* - ",IF(Data!C30="L","*Leave* - ",IF(Data!C30="A","*Absent* - "))))&IF(OR(Data!C30<>"",Data!D30<>""),TEXT(Data!$A$30,"MM/DD/YY - "),"")&IF(OR(Data!C30<>"",Data!D30<>""),TEXT(Data!$B$30,"DDDD - "),"")&IF(Data!E30<>"",Data!E30,"")&" // "&
IF(Data!C31="","",IF(ISNUMBER(LEFT(Data!C31)*1),"*Late* - ",IF(Data!C31="L","*Leave* - ",IF(Data!C31="A","*Absent* - ")))) OR(Data!C31<>"",

 

[jasonb75]

I'm not convinced that anything will work with what you're attempting to do, but with excel 2016 this method is about the best (and shortest) that you will get. I've done the first 2 rows to show you the correct format, the rest is up to you.

=Data!C$1&" => "&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
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* - ")

Note that this must be array confirmed with Ctrl Shift Enter, if you don't do this it will give you a #VALUE! error.

For information, with a newer version of excel (2019 or 365) you could shorten the entire formula to

=Data!C$1&" => "&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CONCAT(
IF(ISNUMBER(LEFT(Data!C3:C31)*1),"£",Data!C3:C31&"")&IF((Data!C3:C31<>"")+(Data!D3:D31<>""),TEXT(Data!A3:A31,"MM/DD/YY - DDDD - "),"")&Data!E3:E31&" // "),
"L","*Leave* - "),"A","*Absent* - "),"£","*Late* - ")

 

[HSAR]

Thank you so much for your answer but i have issue that when the cells will be empty its output will be as like below, I just want that after applying the formula it should not show // // for new row when cells is empty.

Right now above formula ids like MOHSIN => // //

It should be like below one

MOHSIN =>

 

[jasonb75]

Try like this instead

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

 

[HSAR]

Here is Result not mentioning // after new date

MOHSIN => *Late* - 02/01/20 - Saturday - *Leave* - 02/02/20 - Sunday - *Late* - 02/03/20 - Monday -

Using this code.
=SUBSTITUTE(CLEAN(Data!C$1&" => "&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
IF(ISNUMBER(LEFT(Data!C3)*1),"£",Data!C3)&IF(OR(Data!C3:D3<>""),TEXT(Data!A3,"MM/DD/YY - DDDD - "),"")&Data!E3&CHAR(10)&
IF(ISNUMBER(LEFT(Data!C4)*1),"£",Data!C4)&IF(OR(Data!C4:D4<>""),TEXT(Data!A4,"MM/DD/YY - DDDD - "),"")&Data!E4&CHAR(10)&
IF(ISNUMBER(LEFT(Data!C5)*1),"£",Data!C5)&IF(OR(Data!C5:D5<>""),TEXT(Data!A5,"MM/DD/YY - DDDD - "),"")&Data!E5&CHAR(10),
"L","*Leave* - "),"A","*Absent* - "),"£","*Late* - ")),CHAR(10)," // ")

 

[jasonb75]

I was thinking that CLEAN removes consecutive line breaks, but it removes all of them so will not work as needed.

This one should work better,

=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*-"),"-"," - ")

 

[HSAR]

jasonb75

Thank you so much working perfectly

 

[HSAR]

@jasonb75

I am sorry to disturb you again i also need to add Early status in formula i added but it doesn't work please make it correct.


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

 

[jasonb75]

The £ symbol is already in use for late so you need to use a different character for early, perhaps $ or |

It needs to be a symbol that is not going to appear anywhere else in the string so you can't use / or -

You might find that this gets too long again, it might be better to have a helper column, e.g. column H with the part of the formula for row 3 in H3, the part for row 4 in H4, then use those cells to make the longer string.

 

[HSAR]

I made it but its not working and not adding date, weekday and separating the comments with // .

Please see the result

Kashif => Early - Due // to // Rain

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