Absent Date Ranges

[NITIN MANE]

I want to do something similar to this: https://www.mrexcel.com/forum/excel-questions/1002603-absent-dates-need-attendance-sheet.html

But i need one more help with same example.

if any one employee whose continuous absent then answer must be in format of " First Date To Last Date" in that period.

Emp. code	01-Jan-17	02-Jan-17	03-Jan-17	04-Jan-17	05-Jan-17	06-Jan-17	07-Jan-17	08-Jan-17	absent date	Answer Must be
Emp. 001	A	A	A	P	P	A	P	P	01-01-2017, 02-01-2017, 03-01-2017, 06-01-2017	01-01-2017 TO 03-01-2017, 06-01-2017
Emp. 002	P	P	P	P	P	A	A	A	06-01-2017, 07-01-2017, 08-01-2017	06-01-2017, 07-01-2017 to 08-01-2017

<tbody>
</tbody>

 

[Joe4]

OK, creating a function like I did in the other one, here is a function that should work:

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

    Dim cell As Range
    Dim myString As String
    Dim chkAbs As Boolean
    Dim startDate As String
    Dim endDate As String
    
'   Initialize values
    chkAbs = False
    startDate = ""
    endDate = ""
    myString = ""
    
'   Loop through all the cells in the range
    For Each cell In myRange
'       Check for value
        Select Case cell
            Case "A"
                Select Case chkAbs
                    Case False
                        startDate = Format(Cells(myRow, cell.Column), "dd-mm-yyyy")
                        chkAbs = True
                    Case True
                        endDate = Format(Cells(myRow, cell.Column), "dd-mm-yyyy")
                End Select
            Case "P"
                Select Case chkAbs
                    Case True
                        If endDate = "" Then
                            myString = myString & startDate & ", "
                        Else
                            myString = myString & startDate & " to " & endDate & ", "
                        End If
                End Select
                startDate = ""
                endDate = ""
                chkAbs = False
        End Select
    Next cell
    
'   Check to see if last entry was absent and address that
    Select Case chkAbs
        Case True
            If endDate = "" Then
                myString = myString & startDate & ", "
            Else
                myString = myString & startDate & " to " & endDate & ", "
            End If
    End Select
    
'   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.

The first argument in the function is looking for the range to check for the A’s and P’s for that particular row. And the second argument is looking for what row has the title row of dates (assuming the dates are entered as dates and not as text).
So, the formula would look something like:

=ABSENTDAYS(B2:I2,1)