Absent dates need in Attendance Sheet..

[serona72112]

I need absent dates in Attendance Sheet.. How to do?

Emp. code	1	2	3	4	5	6	7	8	absent date
Emp. 001	P	P	A	P	P	A	P	P	????
Emp. 002	A	P	P	P	P	P	A	A	????

<tbody>
</tbody>

answer must be,

Emp. 001 = 3, 6

Emp. 002 = 1, 7, 8

Help me !

Regards,
Serona

 

[Joe4]

Welcome to the Board!

I would probably create my own Function using VBA to do this.
Would that be an acceptable solution for you?

 

[Joe4]

So here is that that UDF would look like:

Function AbsentDays(myRange As Range, myRow As Long) As String

    Dim cell As Range
    Dim myString As String
    
'   Loop through all the cells in the range
    For Each cell In myRange
'       Check for value
        If cell = "A" Then
            myString = myString & Cells(myRow, cell.Column) & ", "
        End If
    Next cell
    
'   Return string
    If Len(myString) > 0 Then
        AbsentDays = Left(myString, Len(myString) - 2)
    End If
    
End Function

Then you would use it like any other function in Excel.
So, let's say that your data example is in the range A1:J3, so that your first row of data is on row 2 and the Ps and As are in range B2:I2 and row 1 has the day numbers.

Then, here is the formula to put in J2:

=absentdays(B2:I2,1)

 

[Fahad Irfan]

Thank you so much I am so great full to you I can't thank you enough I just made this account to thank you.

Your formula Runs Perfectly But It Show Full Dates Like "02-03-2022" but instead I only want The Day Value Like "2,1, 7, 8"
again Thank you so much for your formula.

 

[Joe4]

Welcome to the Board!

Your formula Runs Perfectly But It Show Full Dates Like "02-03-2022" but instead I only want The Day Value Like "2,1, 7, 8"
again Thank you so much for your formula.

If that is the case, then you do not have the same conditions as what the original poster posted.
Note that the first row, they just have plan numbers, like 1,2,3,...

While yours appears to have that, I suspect that is really not the case. I suspect that your first row is really not numbers like that, but rather you have dates with a custom format of "d".
That is the only way my code would be returning dates instead of those numbers.

If that is the case, and you only want to return the day number of your date, then you will need to change this line of code:

            myString = myString & Cells(myRow, cell.Column) & ", "

to this:

            myString = myString & Format(Cells(myRow, cell.Column), "d") & ", "